RedshiftのTIMESTAMP型には2種類あります。
- TIMESTAMP WITHOUT TIME ZONE（以下WITHOUTとも表記）
- TIMESTAMP WITH TIME ZONE（以下WITHとも表記。この記事にSQLの
WITH句は登場しません。）
両者は似ているようで結構違います。
違うことはなんとなく理解してはいましたが、 落ち着いて考えると結構あやふやな理解になっている点もあったので、 一通りまとめていきたいと思います。
言及範囲
ここではRedshiftについての調査に限定しています。 おそらくPostgreSQLでも同じようになっているのかとは思いますが、 未確認ですので、必要に応じて手元での確認をお願いいたします。
また、TIME WITHOUT TIME ZONEとTIME WITH TIME ZONEもあり、 これも同様の動きかと思いますが、未確認です。
では概要から確認していきましょう。
概要
TIMESTAMP WITHOUT TIME ZONE
こちらの方が簡単です。 理解の仕方としては、「ただの文字列」だと思えば良いということです。
もちろん本物の文字列であるVARCHARなどとは別物ですが、 挙動は同じと考えれば良いです。 すなわち、一度Redshiftに書き込まれたら、誰がどう見ようと同じものに見えます。 文字列が見るタイムゾーンなどで変わらないのと同じことです。
名前としてもWITHOUT TIME ZONEですので、 「タイムゾーンとか難しい概念は何もナシよ」と理解すれば良いでしょう。
TIMESTAMP WITH TIME ZONE
こちらは、地球上におけるある瞬間の時刻を表しています。 地球上における「ある瞬間」という概念を考えることができ1て、 その瞬間は、日本では夜の9時で、イギリスでは正午だったりするわけです。 よって、このカラムの値はどのタイムゾーンで見るかによって見える時刻が変わります。
WITHをきちんと理解したい
これだけだと、WITHについてはわかったようでわかりません。 WITHをきちんと理解するためには以下を分けて考える必要があります。
- データを入れるタイミング
- （データが入っている状態）
- データを見るタイミング
クエリ実行環境にはタイムゾーンという概念があり、 WITHのデータを入れる/見るたタイミングにおいてはこのタイムゾーンが使われます。 どのタイムゾーン設定の状態でクエリが実行されるかによって、 格納される値、見える値は変化します。
また、データを入れる時のタイムゾーンと見るときのタイムゾーンは必ずしも一致しません。 これはデータを入れる人と見る人が別であることを考えれば、絶対に制御できない要素とも言えます。 これらは完全に独立であるため、それを理解した上で理解しないとかなり大きな混乱を生みます。
なお、データが入っている状態については、 Redshiftがどうやってデータを持っているかという話なので、通常、ユーザは観測できません。 しかし、これがどうなっているのかを考えると理解がスムーズになりますので、詳しくは後述します。
クエリ実行時のタイムゾーンは何か？
データを入れるタイミングと見るタイミングではタイムゾーンが考慮されると言いましたが、 タイムゾーンはどうやって決まるのでしょうか？
Redshiftでは次の3箇所が参照されるようです。
- クラスタの設定（UTC固定）
- ユーザの設定
- セッションの設定
下に行くほどより具体的な設定となりますので、 下で指定があればそれが使われ、なければより上の値が参照されるようです。 これを考慮した結果、どのタイムゾーンが使われているのかは
SHOW TIMEZONE;で確認できます。
なお、ドキュメント に記載がある通り、クラスタ単位での設定はUTC固定で、変更できません。
また、WITHでデータを挿入する際にはタイムゾーンを明示的に指定することができます。 格納される値が想定外タイムゾーンで格納されて、意図しない値となることは影響度が大きいため、 WITHのカラムにデータを挿入するときは常に明示的に指定した方が良いかと思います。
やってみる
ここまでの話を実際にやってみて確認します。
テーブル準備
WITHOUTとWITH両方を持ったテーブルを準備します。
--drop table tztest;
create table tztest (
name text,
wo timestamp,
w timestamptz
);データ挿入
タイムゾーン変更を明示的に行いつつ、データ挿入します。
-- このセッションのタイムゾーンを`Asia/Tokyo`に設定
set timezone to 'Asia/Tokyo';
-- データを挿入
-- without とwithには同じ文字列を指定して挿入します。
insert into tztest values
('Asia/Tokyoで挿入', '2020-02-02 12:00:00', '2020-02-02 12:00:00');
-- このセッションのタイムゾーンを`UTC`に設定
set timezone to 'UTC';
-- データを挿入
-- without とwithには同じ文字列を指定して挿入します。
insert into tztest values
('UTCで挿入', '2020-02-02 12:00:00', '2020-02-02 12:00:00');データ参照
JSTで見る
set timezone to 'Asia/Tokyo';
select * from tztest;
UTCで見る
set timezone to 'UTC';
select * from tztest;
ここは少し頭が痛くなりそうなところではありますが、 じっくり一つ一つ確認してみてください。
タイミングごとに見てみる
上でやったものを一応振り返ります。
データ挿入時
WITHOUT
WITHOUTはただの文字列なので、タイムゾーン考慮はありません。
2023-02-02 12:00:00で表される時刻を挿入する場合、
その文字列情報がそのまま格納されるイメージで、
2023-02-02 12:00:00がRedshiftに格納されます。
WITH
WITHにおいては、挿入しようとしているクエリに設定されたタイムゾーンが考慮されます。
WITHは先述の通り『地球上における「ある瞬間」』を保存するものですので、
「2023-02-02 12:00:00で表される時刻を挿入する」という内容だけでは情報が足りません。
どのタイムゾーンにおける2023-02-02 12:00:00（と言う瞬間）を格納したいのかを指定する必要があります。
そこでクエリに設定されているタイムゾーンが使われます。
なお先述した通り、WITHに挿入する際にはタイムゾーンの直接指定が可能です。
insert into tztest values
('UTCで挿入', '2020-02-02 12:00:00', '2020-02-02 12:00:00 UTC');データが格納されている状態
WITHOUT
実質文字列なので、特に難しいことはなく、そのまま格納されています。 もちろん文字列ではなくバイナリで格納されているはずです。
WITH
Redshift内部としてはUTCでの値として保存されています。 名前的にはややこしいですが、WITH TIMEZONEですがタイムゾーン情報は格納されません。
これはここまでの話を考えれば当然の話ですが、 地球上におけるある一つの瞬間の情報を格納するので、 どの場所における時刻を記録しておくのかを決めておけば良く、 タイムゾーンに関する情報は格納しておく必要がないことがわかります。 この方がデータ容量の削減になります。
データ観測時
WITHOUT
書かれている情報をそのまま読んで表示します。 くどいですが、実質文字列なのでタイムゾーンという概念はありません。
WITH
どのタイムゾーンで読むかによって、見える値が変わります。 タイムゾーンの選ばれ方はデータ格納時と同様です。 UTCで格納されたデータを、あるタイムゾーンの値に変換したものが見えます。
使い分け
使い分けに方法について私見を書いてみます。
まず前提として、どちらかを使わないと何かが達成できないというようなことはないです。 WITHOUTの情報に「このカラムの時刻はどこどこのタイムゾーンの値とする」と取り決めをしてしまえばどちらも情報量は同じです。 どう扱いたいか、どちらが楽に運用できるか、などの観点でどちらが適切か考えれば良いことになります。
今の所の結論として、私はWIHTOUTのみを使えば良いと思っています。 その際、そのカラムがどのタイムゾーンでの値を格納するのかについては、 迷いなくわかるようにドキュメントに記載するとか、 もしくはカラム名にタイムゾーンを入れてしまうのが良いと思います。
こうするともうWITHと違いがほとんどないようにも思えますが、 WITHには決定的な落とし穴があると思っています。 それは、見る人によって値が違って見えるという事態が発生するしうることです。
ここまでの文章を読んだ方ならその理由は明らかだと思いますが、 WITHで見える値は、見る人のタイムゾーンに依存します。 つまり見る人の設定次第ということです。 これはタイムゾーンによる値の読み間違いが起こるリスクを、 そのシステムを触る人全員にばら撒いていることになります。
正直、タイムゾーン違いによる利用者間での勘違いや、 そこから派生する確認作業などは非常に虚しいものとなります。 これを避けるために少し労力を割くのはとても意味がある行いだと思います。 また、仕組みを理解している人でも、一時的にいつもと違う設定でクエリを流すような時もありますので、 慣れている人でもハマってしまうことがあります。
もちろんWITHOUTでタイムゾーンを明記していたとしても それをきちんと読まなかったりして無視してしまうことも少なからずありえますが、 WITHOUTの場合は見える値が絶対的な正になりますので、状況を確認するのが格段にラクです。 WITHを使う主なメリットは 「このカラムのタイムゾーンは何か」を気にしなくて良いということですが、 このリスクを考えると、特に積極的に利用する動機は見つからないと思っています。
Tips
省略名
二つのTIMESTAMP型はそれぞれ短い省略名を持っています。
TIMESTAMP=TIMESTAMP WITHOUT TIME ZONETIMESTAMPTZ=TIMESTAMP WITH TIME ZONE
TZをわざわざつけないとWITHにならない辺り、 WITHOUTの利用が推奨されてるのではと都合よく考えています。
まとめ
二つのTIMESTAMP型についてまとめてみました。
WITHは一見便利に見えますが、ミスを誘発しやすいかなと思いますので、 WITHOUTだけを使用するのが良さそうに思いました。 ただしWITHOUTについても、そのカラムがどのタイムゾーン前提なのかをはっきりと決め、 迷いなくその情報を使用できる状態にする必要があるという点はお忘れなく。
WITHを使ったカラムについて他人と話をするときは、正確性を期すため、 「UTCで12:00:00をインサートした」「UTCで12:00:00に見える」など、 全ての時刻情報についてタイムゾーンを添えて会話しないと話が通じないことがありえます。 全ての会話にタイムゾーンへの言及が付くので「WITH TIMEZONE」という名前なのかもしれません。
以上、誰かの理解に繋がれば幸いです。
参照情報
- 特殊相対論を考えない世界像においては ↩
4
