Oracle上にあるテーブルをISO 8601形式の日付で範囲指定して絞込検索を行う方法
日付を利用した絞込において、ISO 8601型式の日付を検索に利用すると、タイムゾーンにかかわらず等しい検索結果を得ることができます。
条件
- テーブル上の日付はtimestamp型でセットされている(=DBのタイムゾーンに応じた日付がセットされている)こと
- 検索時に指定する日付はtimestamp_tz型でセットされること
コード
select * from <テーブル名> where sys_extract_utc(<日付列>) >= to_timestamp( sys_extract_utc( to_timezone_tz( '<開始日時(timestamp_tz型で指定)>', 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM' ) ) ) and sys_extract_utc(<日付列>) >= to_timestamp( sys_extract_utc( to_timezone_tz( '<終了日時(timestamp_tz型で指定)>', 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM' ) ) ) order by <日付列> asc;
例
select id, /* pk */ date /* timestamp型 */ from hogetbl where sys_extract_utc(date) >= to_timestamp( sys_extract_utc( to_timestamp_tz( '2019-02-01T00:00:00.000+0000', 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM' ) ) ) and sys_extract_utc(date) >= to_timestamp( sys_extract_utc( to_timestamp_tz( '2019-02-01T23:59:59.000+0000', 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM' ) ) ) order by date asc;