PostgreSQL:タイムゾーン

結論

タイムスタンプには TIMESTAMP WITH TIME ZONE と TIMESTAMP WITHOUT TIME ZONE の2つの型がある。

テーブル定義は必ず TIMESTAMP WITH TIME ZONE で作れ。

WITHOUT TIME ZONE では、ローカル時刻をUTCのように書き込む。

つまり "1970-01-01 00:00:00" を書き込むと、内部的には UTC の "1970-01-01 00:00:00" として書き込まれ、読み出す時にはローカル時刻の "1970-01-01 00:00:00" として読み出される。

何を言っているのか分かりづらいが、「見たままの値が書き込まれ、見たままの値が読み出される」

これはサマータイムが絡むとサマータイム中の時間なのかそうでないのかの判断が付かなくなる。

何も考えずに TIMESTAMP と設定すると TIMESTAMP WITHOUT TIME ZONE になってしまう。

WITH TIME ZONE であれば読み書きは簡単だが、WITHOUT TIME ZONE では大変なことになる。

WITHOUT TIME ZONE で「見たままの時刻」を使う場合はサマータイムの地獄を味わうことになる。

簡単に取り回すためには

time_t 型(UNIX time)は、to_timestamp(n) で渡すことができる。いちいち変換しなくてもよい。

文字列型にする場合はタイムスタンプの文字列は ISO8601 形式にせよ。

PostgreSQL は ISO8601 形式をそのまま取り込める。

PostgreSQL は ISO8601 形式そのままで出力する方法がなく迂回する必要がある。

JavaScript (Node.js) では new Date(str).toISOString() で、一度解析させてから toISOString で整形するのが簡単。

比較の時には getTime を使わなければならない。文字列のままでは比較できない。

何が問題か?

設計者、プログラマが、タイムゾーンの取り扱いの意味を理解しないまま設計しているために、混乱が起きている。

PostgreSQL はそのままでは ISO8601 とは微妙に異なる形式で出力するので、工夫が必要になってしまっている。

"WITH TIME ZONE" という名前がよくないため混乱を引き起こしている。

名前に反してデータには「タイムゾーン」情報は含まれていない。

記録されているのは UTC 時刻で、入出力の時にローカル時刻+当時のローカルでのオフセット時間との間で変換される。

正しくタイムゾーン情報を残したいのであれば、タイムゾーンを timestamp とは別のフィールドに残しておく必要がある。

対応しているタイムゾーンの一覧を見る

code:sql

SELECT * FROM pg_timezone_names;

現在のセッションのタイムゾーンを見る方法

code:sql

show timezone;

code:sql

SELECT current_setting('TIMEZONE'); // set timezone すると変わっている。

現在のセッションにタイムゾーンを設定する方法

code:sql

SET timezone TO 'Asia/Tokyo';

データベースにタイムゾーンを設定する方法

code:sql

ALTER DATABASE yourdb SET timezone TO 'Asia/Tokyo';

-- サーバー(postmaster)のタイムゾーンに合わせる場合

ALTER DATABASE yourdb SET timezone TO default;

default

Q. WITH TIME ZONE にはタイムゾーン情報が保存されているか?

つまり、JSTやESTというタイムゾーン情報が個別に保存されているか?

A. 保存されていない。

内部的には UTC 時刻で保存され、それが読み書きされる時に、現在のセッションのタイムゾーンにおける時差付きのように読み出される。

このため、保存時のタイムゾーンがどれだったのかを残したい場合には、タイムゾーンを示すフィールドが別途必要となる。

ISO8601 では時差しか書かれていないのでタイムゾーンは分からない点に注意。

現在のセッションが Asia/Tokyo (JST) タイムゾーンの時に、2000-01-01 00:00:00 を保存すると、1999-12-31 15:00:00 UTC として保存される。

保存された 1999-12-31 15:00:00 UTC は、現在のセッションが Asia/Tokyo (JST) タイムゾーンの時に読み出すと 2000-01-01 00:00:00+09 として読み出される。

Q. WITH TIME ZONEと WITHOUT TIME ZONE とでデータ量に違いはあるか?

A. ない。

よって通常は WITH TIME ZONE を常に使う方がよい。

WITHOUT TIME ZONE では、タイムゾーンに関係なく「見たまま」の時刻が保存される。

見たままの時刻なので、常にセッションのタイムゾーン上の時刻(要するにローカル時刻)として読み出される。

このため、セッションのタイムゾーンが異なると、時刻がずれることになる。

どう取り扱えばよいのか?

WITH TIME ZONE に入っているのは UTC なので、読み出す側のタイムゾーンに注意する。(たいていの場合は気にしなくてもよい)

文字列として読み出す場合、時差の部分を切り取れば、常に読み出した側のローカル時刻となる。

WITHOUT TIME ZONE に入っているのが UTC の場合

かつセッションタイムゾーンが UTC の場合

UTCとして読み出したい場合は、そのまま読み出せばよい。(テキスト上。時差フィールドが付いていないことに注意)

UTCを書き込みたい場合は、そのまま書き込めばよい。(テキスト上。時差フィールドが付いていないことに注意)

ローカル時刻として読み出したい場合

ローカル時刻を書き込みたい場合

WITHOUT TIME ZONE に入っているのが UTC の場合

かつセッションタイムゾーンがローカル時刻の場合

WITHOUT TIME ZONE に入っているのがローカル時刻の場合

セッションが同じタイムゾーンの場合

ローカル時刻として読み出したい場合、そのまま読み出せばよい。この場合は、時差フィールドなし。

時差フィールドを付ける場合

ローカル時刻として書き込みたい場合、そのまま書き込めばよい。

時差フィールドは無視される。

UTCとして読み出したい場合

値 AT TIME ZONE 'UTC'

UTCで書き込みたい場合

'ISO8601形式の値' AT TIME ZONE 'UTC'

読み出すセッションが異なるタイムゾーンの場合、元のタイムゾーンを AT TIME ZONE 元のタイムゾーン名 で指定する。

書き込むセッションが異なるタイムゾーンの場合

code:test_timezone.sql

create table hoge (f1 char(10) primary key, f2 timestamp);

insert into hoge values ('a', '2020-01-01 00:00:00');

insert into hoge values ('b', '2020-01-01 00:00:00+01');

insert into hoge values ('c', '2020-01-01 00:00:00+09');

insert into hoge values ('d', '2020-01-01 00:00:00' AT TIME ZONE 'UTC');

insert into hoge values ('e', '2020-01-01 00:00:00+01' AT TIME ZONE 'UTC');

insert into hoge values ('f', '2020-01-01 00:00:00+09' AT TIME ZONE 'UTC');

insert into hoge values ('g', CAST('2020-01-01 00:00:00' AS TIMESTAMP WITH TIME ZONE));

insert into hoge values ('h', CAST('2020-01-01 00:00:00+01' AS TIMESTAMP WITH TIME ZONE));

insert into hoge values ('i', CAST('2020-01-01 00:00:00+09' AS TIMESTAMP WITH TIME ZONE));

insert into hoge values ('j', '2020-01-01T00:00:00' AT TIME ZONE 'UTC');

insert into hoge values ('k', '2020-01-01T00:00:00+01:30' AT TIME ZONE 'UTC');

insert into hoge values ('l', '2020-01-01T00:00:00+09:00' AT TIME ZONE 'UTC');

select * from hoge;

f1 | f2

------------+---------------------

a | 2020-01-01 00:00:00

b | 2020-01-01 00:00:00 ← 時差フィールドは無視される

c | 2020-01-01 00:00:00 ← 時差フィールドは無視される

d | 2019-12-31 15:00:00 ← UTC時刻として書き込み

e | 2019-12-31 23:00:00 ← UTC時刻として書き込み(時差フィールドOK)

f | 2019-12-31 15:00:00 ← UTC時刻として書き込み(時差フィールドOK)

g | 2020-01-01 00:00:00 ← ローカル時刻として書き込み

h | 2020-01-01 08:00:00 ← ローカル時刻として書き込み(時差フィールドOK)

i | 2020-01-01 00:00:00 ← ローカル時刻として書き込み(時差フィールドOK)

j | 2019-12-31 15:00:00 ← UTC時刻として書き込み

k | 2019-12-31 22:30:00 ← UTC時刻として書き込み(時差フィールドOK)

l | 2019-12-31 15:00:00 ← UTC時刻として書き込み(時差フィールドOK)

(9 行)

WITHOUT TIME ZONE からの読み出し

code:read_timestamp.sql

select f1, f2 from hoge where f1='a';

f1 | f2

------------+---------------------

a | 2020-01-01 00:00:00 ← 見たままの時刻(時差がないため、不安定)

select f1, CAST(f2 AS TIMESTAMP WITH TIME ZONE) from hoge where f1='a';

f1 | f2

------------+------------------------

a | 2020-01-01 00:00:00+09 ← ローカル時刻が設定されていて、時差フィールドが欲しいならこれでよい。IE11ではパースに失敗する。

-- PostgreSQL 専用でよいならば、PostgreSQL のキャストが使える

select f1, f2::timestamptz from hoge where f1='a';

f1 | f2

------------+------------------------

a | 2020-01-01 00:00:00+09 ← ローカル時刻が設定されていて、時差フィールドが欲しいならこれでよい。IE11ではパースに失敗する。

select f1, f2 AT TIME ZONE 'UTC' from hoge where f1='a';

f1 | timezone

------------+------------------------

a | 2020-01-01 09:00:00+09 ← UTC時刻が設定されている場合は、これでよい。IE11ではパースに失敗する。

select f1, to_char(f2, 'YYYY-MM-DD"T"HH24:MI:SSZ') from hoge where f1='a';

f1 | to_char

------------+----------------------

a | 2020-01-01T00:00:00Z ← 力技。UTC時刻が設定されている場合はこれでよい。

-- PostgreSQL (9.4以降)専用でよいならば、to_json 関数が使える。ダブルクォーテーションがあることに注意。

select f1, to_json(f2) from hoge where f1='a';

f1 | to_json

------------+-----------------------

a | "2020-01-01T00:00:00" ← 時差フィールドなし。

select f1, to_json(f2::timestamptz) from hoge where f1='a';

f1 | to_json

------------+-----------------------------

a | "2020-01-01T00:00:00+09:00" ← ローカル時刻が設定されているなら、これでよい。

select f1, to_json(f2 AT TIME ZONE 'UTC') from hoge where f1='a';

f1 | to_json

------------+-----------------------------

a | "2020-01-01T09:00:00+09:00" ← UTC時刻が設定されているなら、これでよい。

select f1, to_json(f2)#>>'{}' from hoge where f1='a';

f1 | ?column?

------------+---------------------

a | 2020-01-01T00:00:00

select f1, to_json(f2 AT TIME ZONE 'UTC')#>>'{}' from hoge where f1='a';

f1 | to_json

------------+-----------------------------

a | 2020-01-01T09:00:00+09:00 ← UTC時刻が設定されているなら、これでよい。

"T" を付けるか付けないか?

JavaScript では "T" がなくても解釈してくれるものとしてくれないものとがある。

(仕様で明確に決まっていない。)

Node.js では解釈してくれる。

IE11では解釈してくれない。

code:ie11.separtor.parse.js

new Date("1970-01-01 09:00:00+09:00").getTime()

// NaN

new Date("1970-01-01T09:00:00+09:00").getTime()

// 0

時差をどこまで書くか?

ISO8601 では、時差を時間の所まで書く方式も認められている。

IE11では、分まで書かないと解釈してくれない。

code:ie11.parse.js

new Date("1970-01-01T09:00:00+09").getTime()

// NaN

new Date("1970-01-01T09:00:00+09:00").getTime()

// 0

サマータイムはどう取り扱われるのか?

DATE から DATE の読み書き、あるいは TIME から TIME の読み書きの場合には何も変化しない。

WITHOUT TIME ZONE で書き込まれてしまった時間はサマータイムだったかどうかはもはや分からない。

二重に存在する時刻と飛ばされた時刻が現れてしまう。

テーブルの列が WITH TIME ZONE か WITHOUT TIME ZONE のどちらかを確認する方法

PostgreSQL

\d テーブル名 (素直にそのまま読む)

SELECT pg_typeof(列名) FROM テーブル名 LIMIT 1

値が WITH TIME ZONE か WITHOUT TIME ZONE のどちらかを確認する方法

PostgreSQL

SELECT pg_typeof(結果) FROM ...

code:current_timestamp_type:sql

SELECT pg_typeof(current_timestamp);

pg_typeof

--------------------------

timestamp with time zone

参考