create table public.test1(
date_col date
);
insert into test1(date_col) values('2022-01-01');
insert into test1(date_col) values('2022-02-01');
insert into test1(date_col) values('2022-03-01');
insert into test1(date_col) values('2022-04-01');
insert into test1(date_col) values('2022-05-01');
insert into test1(date_col) values('2022-06-01');
insert into test1(date_col) values('2022-07-01');
insert into test1(date_col) values('2022-08-01');
insert into test1(date_col) values('2022-09-01');
insert into test1(date_col) values('2022-10-01');
insert into test1(date_col) values('2022-11-01');
insert into test1(date_col) values('2022-12-01');
select * from public.test1;
date_col
2022-01-01
2022-02-01
2022-03-01
2022-04-01
2022-05-01
2022-06-01
2022-07-01
2022-08-01
2022-09-01
2022-10-01
2022-11-01
2022-12-01
(12 rows)
dev=# select '20220801' - 100;
?column?
2022-04-23
(1 row)
dev=# select '20220801'::date - 100;
?column?
2022-04-23
(1 row)
dev=# select '20220801'::numeric - 100;
?column?
20220701
(1 row)
dev=# select ('20220801'::numeric - 100)::date;
ERROR: cannot cast type numeric to date
dev=# select * from test1 where date_col::date > ('20220801'::numeric - 100)::date;
ERROR: cannot cast type numeric to date
dev=# select * from test1 where date_col::numeric > ('20220801'::numeric - 100)::numeric;
ERROR: cannot cast type date to numeric
dev=# select * from test1 where date_col > '20220801'::numeric - 100;
date_col
(0 rows)
dev=# select '20220801' - (100 :: NUMERIC || ' day') :: interval;
?column?
134 days 00:53:21
(1 row)
dev=# select '20220801'::date - (100 :: NUMERIC || ' day') :: interval;
?column?
2022-04-23 00:00:00
(1 row)
dev=# select '20220801'::interval - (100 :: NUMERIC || ' day') :: interval;
?column?
134 days 00:53:21
(1 row)