読者です 読者をやめる 読者になる 読者になる

misc.log

日常茶飯事とお仕事と

PostgreSQLのtimestamp型、timezone有無はどう関係する?

PostgreSQLで年月日時刻を扱うにはtimestamp型を使うようですが、これには

  • timestamp with timezone(timestamptzと略記される)
  • timestamp without timezone

の2通りがあります。どちらを使うべきなんでしょうか?

試してみる

こんな感じでテーブルを作ってみます。

create table
	datetest (
		dataid integer,
		testdate timestamp with time zone,
		constraint pkey primary key (dataid, testdate)
		);

整数と時刻(timestamptz)を持つだけのテーブル。

ここにデータを2件入れます。

-- タイムゾーン指定無し→日本時間で登録される(インサート文は12時だがUTCでは3時)
insert into datetest values(1,'2015-09-11 12:00:00')
-- UTC指定で12時
insert into datetest values(2,'2015-09-11 12:00:00 UTC')

そしてSELECTしてみます。

f:id:frontline:20150911110741p:plain

どういうこと?

「2015-09-11 21:00:00+09」という表示は「21時だけど、UTCからは9時間進めてるよ」という意味。要するに日本時間だと21時だけどUTCだと12時だよ、という意味です。どうやら、timezone指定のtimestamp型の場合、DB内部ではUTCで保存され、表示や検索の際にはアクセスしてきているクライアントのシステム時刻設定に合わせて時刻をずらしているようですね。その代り、結果の末尾に「こんだけずらしたよ」ということを表す「+09」などを付けてくれていると。

たとえばこの状況で「21時のデータを検索」してみると

f:id:frontline:20150911111338p:plain

きちんと日本時間で検索に応じてくれます。これならば、とりあえずtimezone付きにしておいても支障はなさそうですね。

参考サイト

Qiitaのこの投稿をベースに確認と検証をしました。キーとして使うときにタイムゾーン情報がユニークさに影響しないか?という話です。参考になりました。

qiita.com


実践PostgreSQL

実践PostgreSQL