概略

DATE型とTIMESTAMP型のデータの比較時に、一部想定外の動きがあったため確認しました。
結論から言うとコンマ秒数まで比較対象のようで、TIMESTAMP型が0.001秒でも大きいor小さい値だと以上or以下の比較時にTRUEになりませんでした。
本記事では確認作業をまとめていきます。

なおSQLはSnowflake上で実行しております。

やりたいこと

date_tableテーブルとtime_tableを下記の条件で比較し、結合結果を確認します。
①同じid
②日付が開始日付以上終了日付以下

事前準備

比較用にDATE型列を持つテーブルとTIME_STAMP型列を持つテーブルを作成します。
Snowflakeでは３種類のTIMESTAMP型がありますが、ここではTIMESTAMP_TZを指定します。
詳しくは下記の公式ドキュメントを参照してください。
Snowflake公式ドキュメント[日付と時刻のデータ型-TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ]
https://docs.snowflake.com/ja/sql-reference/data-types-datetime


--DATE型テーブル
CREATE OR REPLACE TABLE date_table(
id varchar(1),
date_test date                   --日付列
)
;

--TIMESTAMP型テーブル
CREATE OR REPLACE TABLE time_table(
id varchar(1),
start_tz_test timestamp_tz,      --開始日付列
end_tz_test timestamp_tz         --終了日付列
)
;

データを投入していきます。
比較用に、TIMESTAMPテーブルのid = 'a'は日付指定のみ、 id = 'b'は時間まで指定し、start_tz_test列に0.001秒を加えます。

insert into date_table values
('a', '2023-04-01'),
('b', '2023-04-01')
;

insert into time_table values
('a', '2023-04-01', '2023-04-30'),
('b', '2023-04-01 00:00:00.001', '2023-04-30 00:00:00.000')
;

比較確認

２つのテーブルを結合し、取得結果を確認します。


select * from date_table as d
left outer join time_table as t
on d.id = t.id
and d.date_test >= t.start_tz_test
and d.date_test <= t.end_tz_test
;

--取得結果

ID	DATE_TEST	ID_2	START_TZ_TEST	END_TZ_TEST
a	2023-04-01	a	2023-04-01 00:00:00.000 -0700	2023-04-30 00:00:00.000 -0700
b	2023-04-01

id = 'b'は結合ができてませんでした。
これは、比較時に日付型の値が2023年4月1日になった瞬間(='2023-04-01 00:00:00.000')のタイムスタンプ扱いとなり、開始日付列の値よりも0.001秒前となったため結合条件を満たせなくなったと考えられます。

対策

日付までの比較で時間以下の単位は考慮不要の場合は、TIMESTAMP型を日付に変換してあげてから比較するのが無難です。

select * from date_table as d
left outer join time_table as t
on d.id = t.id
and d.date_test >= date(t.start_tz_test)        --date()で日付型に変換
and d.date_test <= date(t.end_tz_test)
;

--取得結果

ID	DATE_TEST	ID_2	START_TZ_TEST	END_TZ_TEST
a	2023-04-01	a	2023-04-01 00:00:00.000 -0700	2023-04-30 00:00:00.000 -0700
b	2023-04-01	b	2023-04-01 00:00:00.001 -0700	2023-04-30 00:00:00.000 -0700

id ='b'も取得できました。