SQLでtimestamptz型をDATE型にキャストする方法と注意点

結論

• CAST(column_name AS DATE)でDATE型にキャストできる
• タイムゾーンを指定する
  • AT TIME ZONE 'timezone_name'でSQL文で指定する
  • OR
  • SET SESSION timezone TO 'timezone_name';でセッションのタイムゾーンを指定する

CAST(created_at AT TIME ZONE 'Asia/Tokyo' AS DATE)

注意点：DATE型にはタイムゾーンの概念がない

timestamptz型にはタイムゾーンの概念がありますが、DATE型にはありません。(当たり前ですが)

そのため以下のように、セッションのタイムゾーンがUTCだった場合などには、日本時間的には2000-01-04の場合でも2000-01-03としてキャストされてしまいます。

CAST(created_at AS DATE)
-- 2000-01-03 16:00:00+00:00 (2000-01-04 00:00:09+00:00) => 2000-01-03

アプリケーションサーバーではJTCとして動作しているけど、SQLを実行するときにセッションがUTCになっていたりするので、思わぬ不具合を起こします。

そのためtimestamptz型をDATE型に変換するときは注意が必要です。