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

2023/01/09に公開約600字

結論

  • 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型に変換するときは注意が必要です。

Discussion

ログインするとコメントできます