Timestamptzについて学んだこと

April 05, 2025
Postgresql

PostgreSQLにはtimestamptz型というタイムゾーン付きのtimestampがあります。

ユーザーが日本国内限定のアプリケーションであれば日時を扱うデータの型はdatetime型やtimestamp型で十分かもしれませんが、

世界中のユーザーを扱うアプリケーションの場合はtimestamptz型が必要なケースも出てくるかと思います。

例えば、Slack等のグローバルアプリでは

世界中のユーザーが自分のタイムゾーンで日時を見る必要がある
送信時刻、投稿時刻、通知時刻などを UTCで一貫して保存し、表示時にローカル時間に変換する必要がある

また、世界的なECサイトを考えた時に

セール開始・終了日時をUTCで保存しておき、ユーザーのタイムゾーンに合わせて開始・終了日時を表示する
注文日時がセールの期間内なのかどうかを判定する処理で、ユーザーのローカル日時をUTCに変換してから終了日時(UTC)と比較し、期限切れであればBad Request(400)を返す、というようなAPIを作る

というような場面が想像が出来ます。

timestamptzについて学んだことを纏めていきます。

timestamptz型のカラムにUTCで保存される

DBeaverやTable Plusで確認すると、2025-04-03 09:00:00.000 +0900のようにJST(日本時間)で表示されます。

+0900がUTCとの時差(9時間)なので、日本時間という意味になります。

え、JSTで保存されている？と思うかもしれませんが、これはDBeaver等のDBクライアントのタイムゾーン設定がJSTになっているため、そのように表示されているだけで、ターミナルでpsql -d db名等のコマンドで接続して確認すると、

下記のように+00(UTC(協定世界時)との時差が0である)となっており、内部的にはUTCで保存されていることが確認出来ます。

2025-04-03 00:00:00+00

検索クエリにヒットさせるには日時をISO8601形式でセットする

ISO8601形式のサンプルを下記に3パターン載せました。末尾がzだったらutc(世界標準時間)で、日本は+9時間の差があり、ニューヨークは-4時間差がある、ということを示しています。

-- utc(世界標準)時間
2025-04-03T14:30:00Z
-- JST(日本標準時)
2025-04-03T14:30:00+0900
-- ニューヨーク時間
2025-04-03T14:30:00-0400

以下のように、〇〇の国の時間基準でYYYY年XX月YY日に登録したユーザーを検索することもできます。

-- 日本時間で2025年4月3日に登録したユーザーを取得する検索クエリは下記のようになります。
select
	"users"."created_at"
from
	"users"
where
	"users"."created_at" >= '2025-04-03T00:00:00.000+0900'
	and "users"."created_at" <= '2025-04-03T23:59:59.999+0900';

-- ニューヨーク時間で2025年4月3日に登録したユーザーを取得する検索クエリは下記のようになります。
select
	"users"."created_at"
from
	"users"
where
	"users"."created_at" >= '2025-04-03T00:00:00.000-0400'
	and "users"."created_at" <= '2025-04-03T23:59:59.999-0400';

フロントエンドからISO8601形式で日時をバックエンドにPOSTする必要がある

ユーザー検索機能があるとして、2025年4月3日に登録したユーザーを取得したい場合、どのタイムゾーンで2025年4月3日なのか、という情報をISO8601形式でフロントエンド(ブラウザ)からバックエンドに送信し、バックエンドは受け取った日時で検索クエリを発行する、という流れになります。

-- JST(日本標準時)
2025-04-03T14:30:00+0900

検索結果はUTCなのでフロントエンドでタイムゾーン変換して表示する

検索結果の日時はUTCなので、2025-04-03T00:00:00.000Zという値がレスポンスでフロントエンドに返されます。

下記の様に、フロントエンドではUTCをjavascriptのDate(‘UTCの日時’).toLocaleString()でブラウザに設定されているタイムゾーンに変換して表示します。

console.log(new Date('2025-04-07T15:00:00Z').toLocaleString());
2025/4/8 0:00:00

以上です