PostgreSQL EXTRACT(EPOCH) ガイド

PostgreSQLは、適切なタイムゾーン処理が組み込まれた堅牢なtimestampサポートを持っています。EXTRACT(EPOCH FROM ...)関数はtimestampをUnix time（epochからの秒数）に変換し、to_timestamp()はその逆を行います。

基本操作：

-- 現在のUnix timestamp取得（秒、小数部あり）
SELECT EXTRACT(EPOCH FROM NOW());            -- 1700000000.123456

-- 整数の秒に変換
SELECT FLOOR(EXTRACT(EPOCH FROM NOW()));     -- 1700000000

-- 特定のtimestampをepochに変換
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-01-15 09:30:00 UTC');

-- Unix timestampをPostgreSQLのtimestampに変換
SELECT to_timestamp(1700000000);
-- '2023-11-14 22:13:20+00'

-- 演算：1時間後のtimestamp
SELECT NOW() + INTERVAL '1 hour';

timestamp vs timestamptz：

PostgreSQLには2つのtimestamp型があります：timestamp（タイムゾーンなし）とtimestamptz（タイムゾーンあり）。よくある誤解に反して、timestamptzはタイムゾーンを格納しているわけではありません。値をUTCで格納し、セッションのtimezone設定に基づいて表示時に変換します。

SET timezone = 'America/New_York';
SELECT '2024-01-15 09:30:00 UTC'::timestamptz;
-- '2024-01-15 04:30:00-05'  (ET東部時間に変換)

SET timezone = 'Asia/Tokyo';
SELECT '2024-01-15 09:30:00 UTC'::timestamptz;
-- '2024-01-15 18:30:00+09'  (JSTに変換)

ベストプラクティス — 常にtimestamptzを使用する： PostgreSQLのドキュメント自体が、ほぼすべてのユースケースでtimestamptzを推奨しています。タイムゾーンなしのtimestampを使用すると、UTC変換なしでリテラル値が保存されるため、サーバーやクライアントが異なるタイムゾーンにいる場合に曖昧になります。

よくある落とし穴：

EXTRACT(EPOCH FROM ...)は整数ではなくdouble precisionの値を返します。比較を行う際にはこれが重要です。正確な整数比較にはFLOOR()を使用するかBIGINTにキャストしてください。また、to_timestamp()は常にtimestamptzを返すため、timestampカラムに代入する際はタイムゾーン変換に注意が必要です。

ミリ秒timestamp： PostgreSQLのtimestamptzはマイクロ秒精度を持ちます。ミリ秒でepochを取得するには：FLOOR(EXTRACT(EPOCH FROM NOW()) * 1000)。