ablog

不器用で落着きのない技術者のメモ

Redshift のDATE型の暗黙的キャストについて

  • テーブル作成
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)