0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@shalXXXX(shal 0618)

PostgreSQL の timestamp と timestamptz の違い：TimeZone を変えて保存値と表示を確かめる

0
Posted at

PostgreSQL の日時型には timestamp（timestamp without time zone）と timestamptz（timestamp with time zone）があります。名前から「timestamptz はタイムゾーンを一緒に保存する型」と思われがちですが、実際にはどちらもタイムゾーンを保存しません。違うのは、入力の解釈と表示のしかたです。

この記事では、同じ値を両方の型に入れ、セッションの TimeZone を変えたときに何が変わるかを実際に確かめます。

検証はすべて PGlite 0.5.8（PostgreSQL 18.3 の WebAssembly 版）で行いました。PGlite の TimeZone の初期値は Etc/GMT0 だったため、各例では SET TimeZone で明示的に指定しています。

結論

timestamp timestamptz
保存するもの 日付と時刻（壁時計の値） UTC に換算した時刻
入力のオフセット（+09 など） 黙って捨てる UTC への換算に使う
表示 保存した値そのまま セッションの TimeZone に換算
サイズ 8 バイト 8 バイト

「ある瞬間」を記録する列（作成日時・更新日時・ログの時刻など）には timestamptz を使うのが安全です。

同じ値を入れて TimeZone を変えてみる

TimeZone = 'Asia/Tokyo' のセッションで、オフセットなしとオフセット付き（+00）の 2 行を入れます。

SET TimeZone = 'Asia/Tokyo';

CREATE TABLE events (id int PRIMARY KEY, ts timestamp, tstz timestamptz);
INSERT INTO events VALUES (1, '2026-10-04 10:00:00',    '2026-10-04 10:00:00');
INSERT INTO events VALUES (2, '2026-10-04 10:00:00+00', '2026-10-04 10:00:00+00');

TimeZone を変えながら同じ SELECT を実行した結果です。

SELECT id, ts, tstz FROM events ORDER BY id;

-- TimeZone = 'Asia/Tokyo'
 id |         ts          |          tstz
----+---------------------+------------------------
  1 | 2026-10-04 10:00:00 | 2026-10-04 10:00:00+09
  2 | 2026-10-04 10:00:00 | 2026-10-04 19:00:00+09

-- TimeZone = 'UTC'
  1 | 2026-10-04 10:00:00 | 2026-10-04 01:00:00+00
  2 | 2026-10-04 10:00:00 | 2026-10-04 10:00:00+00

-- TimeZone = 'America/New_York'
  1 | 2026-10-04 10:00:00 | 2026-10-03 21:00:00-04
  2 | 2026-10-04 10:00:00 | 2026-10-04 06:00:00-04

ここから次のことがわかります。

  • timestamp は、どの TimeZone でも入れたときの文字どおりに表示される
  • timestamp に +00 を付けて入れても、オフセットはエラーにならずに捨てられる（1 行目と 2 行目が同じ値になっている）
  • timestamptz は、オフセットがなければセッションの TimeZone（ここでは日本時間）として解釈し、表示のたびに現在の TimeZone に換算する

オフセットが捨てられるのは、ISO 8601 の Z 付きでも同じです。

SELECT '2026-10-04 10:00:00+09'::timestamp;  -- 2026-10-04 10:00:00
SELECT '2026-10-04T10:00:00Z'::timestamp;    -- 2026-10-04 10:00:00

中身は UTC の時刻だけ

extract(epoch FROM ...) で 1970-01-01 00:00:00 UTC からの秒数を取り出すと、保存されている値がわかります。

SELECT id, extract(epoch FROM ts) AS ts_epoch, extract(epoch FROM tstz) AS tstz_epoch
FROM events ORDER BY id;

 id |      ts_epoch     |     tstz_epoch
----+-------------------+-------------------
  1 | 1791108000.000000 | 1791075600.000000
  2 | 1791108000.000000 | 1791108000.000000

この結果は TimeZone を Asia/Tokyo にしても UTC にしても同じでした。timestamptz の 1 行目は 2 行目より 32,400 秒（9 時間）前、つまり 2026-10-04 01:00:00 UTC として保存されています。どちらの型も pg_column_size は 8 バイトで、タイムゾーン名やオフセットを持つ場所はありません。

timestamp で困る場面

DEFAULT now() がセッションの TimeZone で変わる

now() は timestamptz を返します。timestamp の列に入れると、その時点のセッションの TimeZone で壁時計の値に変換されます。

CREATE TABLE t (id int, ts timestamp DEFAULT now(), tstz timestamptz DEFAULT now());

SET TimeZone = 'Asia/Tokyo';
INSERT INTO t (id) VALUES (1);
SET TimeZone = 'UTC';
INSERT INTO t (id) VALUES (2);

SELECT id, ts, tstz FROM t ORDER BY id;  -- TimeZone = 'UTC' で表示

 id |           ts            |            tstz
----+-------------------------+----------------------------
  1 | 2026-10-04 07:01:57.253 | 2026-10-03 22:01:57.253+00
  2 | 2026-10-03 22:01:57.258 | 2026-10-03 22:01:57.258+00

2 行はほぼ同時（差は 5 ミリ秒）に入れたのに、ts は 9 時間ずれています。アプリサーバー・バッチ・管理ツールで接続時の TimeZone が違うと、timestamp の列には基準の異なる値が混ざり、あとから区別できません。

timestamp と timestamptz の比較も TimeZone 次第

2 つの型を比べたり変換したりすると、timestamp は現在の TimeZone の時刻として扱われます。

SELECT timestamp '2026-10-04 10:00:00' = timestamptz '2026-10-04 10:00:00+09';

-- TimeZone = 'Asia/Tokyo' → true
-- TimeZone = 'UTC'        → false

同じクエリでも、接続の設定によって結果が変わります。

ALTER COLUMN TYPE timestamptz は実行時の TimeZone で解釈される

既存の timestamp 列を timestamptz に変えるとき、USING を付けないと、ALTER を実行したセッションの TimeZone で既存の値が解釈されます。

-- 中身は UTC のつもりで入れた created_at（timestamp）
SET TimeZone = 'UTC';
CREATE TABLE logs (id int, created_at timestamp);
INSERT INTO logs VALUES (1, '2026-10-04 10:00:00');
CREATE TABLE logs2 AS SELECT * FROM logs;

-- 日本時間のセッションで型を変える
SET TimeZone = 'Asia/Tokyo';
ALTER TABLE logs  ALTER COLUMN created_at TYPE timestamptz;
ALTER TABLE logs2 ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'UTC';

SET TimeZone = 'UTC';
SELECT 'logs', created_at FROM logs
UNION ALL
SELECT 'logs2', created_at FROM logs2;

 logs  | 2026-10-04 01:00:00+00   -- 9 時間ずれた
 logs2 | 2026-10-04 10:00:00+00   -- 意図どおり

移行するときは、既存の値がどのタイムゾーンの時刻なのかを確かめ、USING 列 AT TIME ZONE '<そのタイムゾーン>' を明示します。

AT TIME ZONE は型によって向きが逆になる

AT TIME ZONE は、左側の型によって結果の型が入れ替わります。

SELECT timestamptz '2026-10-04 01:00:00+00' AT TIME ZONE 'Asia/Tokyo';
-- 2026-10-04 10:00:00        （timestamp without time zone）

SELECT timestamp '2026-10-04 10:00:00' AT TIME ZONE 'Asia/Tokyo';
-- 2026-10-04 01:00:00+00     （timestamp with time zone。TimeZone = 'UTC' で表示）
  • timestamptz AT TIME ZONE 'X'：その瞬間は X では何時か（壁時計の値）を返す
  • timestamp AT TIME ZONE 'X'：その壁時計の値を X の時刻とみなして、瞬間を返す

夏時間のある地域では interval の足し方が変わる

America/New_York は 2026-11-01 の 2:00 に夏時間が終わり、1 時間戻ります。この日をまたいで足し算をすると、型によって結果が変わります。

SET TimeZone = 'America/New_York';

SELECT timestamptz '2026-11-01 00:30:00' + interval '1 day'    AS tstz_1day,
       timestamptz '2026-11-01 00:30:00' + interval '24 hours' AS tstz_24h,
       timestamp   '2026-11-01 00:30:00' + interval '1 day'    AS ts_1day,
       timestamp   '2026-11-01 00:30:00' + interval '24 hours' AS ts_24h;

       tstz_1day        |        tstz_24h        |       ts_1day       |       ts_24h
------------------------+------------------------+---------------------+---------------------
 2026-11-02 00:30:00-05 | 2026-11-01 23:30:00-05 | 2026-11-02 00:30:00 | 2026-11-02 00:30:00

timestamptz では '1 day' は「翌日の同じ時刻」、'24 hours' は「24 時間後」になり、この日は 1 時間ずれます。timestamp は夏時間を知らないので、どちらも同じ結果です。引き算も同様で、timestamptz では 2026-11-01 00:00 から 11-02 00:00 までが 1 day 01:00:00、timestamp では 1 day になりました。

日本（Asia/Tokyo）には現在夏時間がないので、この差は出ません。海外のユーザーの時刻を扱うときに気をつける点です。

日本時間の「日付」で検索する

timestamptz を日付に変換する tstz::date は TimeZone によって結果が変わるため、式インデックスに使えません。

CREATE INDEX ON events ((tstz::date));
-- ERROR: functions in index expression must be marked IMMUTABLE

date_trunc('day', ...) も同じで、TimeZone = 'Asia/Tokyo' では 2026-10-04 00:00:00+09、'UTC' では 2026-10-04 00:00:00+00 と、別の瞬間を返しました。

タイムゾーンを式の中で固定すれば、結果が一意に決まるのでインデックスを作れます。

CREATE INDEX ev_jst_date ON ev (((tstz AT TIME ZONE 'Asia/Tokyo')::date));

EXPLAIN (COSTS OFF)
SELECT count(*) FROM ev
WHERE (tstz AT TIME ZONE 'Asia/Tokyo')::date = '2026-03-01';

Aggregate
  ->  Index Scan using ev_jst_date on ev
        Index Cond: (((tstz AT TIME ZONE 'Asia/Tokyo'::text))::date = '2026-03-01'::date)

（ev は 1 分ごとの時刻を 20 万行入れたテーブルで、この日の件数は 1,440 件でした。）検索側も、インデックスと同じ式を書く必要があります。

timestamp を使ってよい場面

timestamp は「タイムゾーンに関係なく、その壁時計の値そのもの」を表したいときの型です。

  • 「毎日 9:00 に通知」のような、地域ごとの現地時刻で決まる予定（タイムゾーン名は別の列に持つ）
  • 店舗の営業開始時刻のように、どの地域から見ても同じ数字で表示したい値

それ以外の「いつ起きたか」を記録する列は timestamptz にしておくと、接続の設定やサーバーの移設で値の意味が変わりません。

まとめ

  • timestamptz もタイムゾーンは保存しない。UTC に換算した時刻を 8 バイトで持ち、表示のたびにセッションの TimeZone に換算する
  • timestamp は入力の +09 や Z を黙って捨てる
  • timestamp の列に now() を入れる、timestamptz と比べる、timestamptz に型変更する、のいずれもセッションの TimeZone で結果が変わる
  • 型変更では USING 列 AT TIME ZONE '...' で元の基準を明示する
  • 日本時間の日付で検索するなら (tstz AT TIME ZONE 'Asia/Tokyo')::date の式インデックスを使う

筆者は、DBML でスキーマを書いて ER 図・DDL・テーブル定義書を生成する DB 設計ツール「Joinery」を開発しています。列の型の選び方を含む DB 設計の手順は DB 設計ガイド にまとめています。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?