PostgreSQLでのtimestampとtime zone

背景

最近PostgreSQLを使ったRailsアプリのデータをredashで眺めようとしている時に、
local timeでの日ごとの集計を取ろうとして混乱したので、PostgreSQLでのtimestampとtime zoneについてまとめてみる。

PostgreSQL 9.6.2で確認した。
基本的にPostgreSQL: Documentation: 9.6: Date/Time Functions and Operatorsに書いてあることである。

コネクションのタイムゾーン

postgresql.confで設定されたもの、されていない場合にはバージョンによって違うデフォルトの設定になる。

foo=# set timezone to 'UTC';
SET
foo=# show timezone;
 TimeZone
----------
 UTC
(1 row)

以下ではUTCに設定されているのを前提とする。

timestampとtimestamptz

タイムスタンプの型には

timestamp [without time zone]
timestamp with time zone(timestamptz)

がある。

現在時刻のタイムスタンプを得る場合にもそれぞれの型のlocaltimestampとcurrent_timestampがある。

foo=# SELECT pg_typeof(localtimestamp), localtimestamp;
          pg_typeof          |         timestamp
-----------------------------+---------------------------
 timestamp without time zone | 2017-10-21 17:23:30.07992
(1 row)

foo=# SELECT pg_typeof(current_timestamp), current_timestamp;
        pg_typeof         |             now
--------------------------+------------------------------
 timestamp with time zone | 2017-10-21 17:23:43.85508+00
(1 row)

foo=# SELECT current_timestamp = localtimestamp;
 ?column?
----------
 t
(1 row)

localtimestampはlocalの名の通りコネクションに設定されたタイムゾーンに応じた日時になる。
つまり上の例でもしset timezone to 'Japan';されていると2017-10-22 02:23:30.07992になる。

timestamptzの値はコネクションのタイムゾーンに応じて表示される。
つまり上の例でもしset timezone to 'Japan';されているなら2017-10-21 02:23:43.85508+09と表示される。

AT TIME ZONE

タイムゾーンを変換するにはAT TIME ZONEconstructを使う。

timestamp AT TIME ZONE zone

timestamp_value AT TIME ZONE 'TIME_ZONE_NAME'はtimestamp_valueを、
TIME_ZONE_NAMEで日時がtimestamp_valueであるとしたtimestamptzに変換する。

foo=# SELECT localtimestamp, pg_typeof(localtimestamp AT TIME ZONE 'Japan'), localtimestamp AT TIME ZONE 'Japan';
         timestamp          |        pg_typeof         |           timezone
----------------------------+--------------------------+-------------------------------
 2017-10-21 17:23:59.742512 | timestamp with time zone | 2017-10-21 08:23:59.742512+00
(1 row)

この例ではlocaltimestamp AT TIME ZONE 'Japan'は
2017-10-21 17:23:59.742512に+09のtimezone情報が追加されたtimestamptz、2017-10-21 17:23:59.742512+09 = 2017-10-21 08:23:59.742512+00となる。

timestamptz AT TIME ZONE zone

timestamptz_value AT TIME ZONE 'TIME_ZONE_NAME'はtimestamptz_valueを
TIME_ZONE_NAMEでの日時としてのtimestampに変換する。

foo=# SELECT current_timestamp, pg_typeof(current_timestamp AT TIME ZONE 'Japan'), current_timestamp AT TIME ZONE 'Japan';
              now              |          pg_typeof          |          timezone
-------------------------------+-----------------------------+----------------------------
 2017-10-21 17:24:16.533614+00 | timestamp without time zone | 2017-10-22 02:24:16.533614
(1 row)

この例ではcurrent_timestamp AT TIME ZONE 'Japan'は
2017-10-21 17:24:16.533614+00 = 2017-10-22 02:24:16.533614+09なので、そこからtimezoneの情報を取り除いたtimestampの2017-10-22 02:24:16.533614になる。

local timeでの日時ごとの集計

以上のことから当初の問題を考えてみる。
Railsでは特に考えないで使っているとtimestampの型はtimestamp [ without time zone ]になっている。
従ってやりたいことは、timestamp型でUTCの日時でデータが入っているものを、日本時間での日付のdateに変えること。

結果から言うとDATE(created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Japan')のようになる。
例えばcreated_atが2017-10-21 17:24:16.533614なら、
created_at AT TIME ZONE 'UTC'は2017-10-21 17:24:16.533614+00(=2017-10-22 02:24:16.533614+09)に、
created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Japan'は2017-10-22 02:24:16.533614に、
結果DATE(created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Japan')は2017-10-22と目的のものになる。

foo.bar_countを作成日ごとに集計したい場合にはSQLクエリは

SELECT
  DATE(created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Japan'),
  SUM(bar_count)
FROM foo
GROUP BY 1;

のようになる。