PostgreSQL「timestamp out of range」の原因と対処

-- JavaScript の "ミリ秒" をそのまま渡してしまう例
SELECT to_timestamp(1692873600000);  -- NG: 多くの環境で 'timestamp out of range'

-- 修正：秒に直す（浮動小数で割る）
SELECT to_timestamp(1692873600000/1000.0);  -- OK

-- "マイクロ秒" なら 1e6 で割る
SELECT to_timestamp(1692873600000000/1000000.0); -- OK

-- 安全変換（桁数で自動判定）
WITH v(n) AS (VALUES (1692873600::bigint), (1692873600000), (1692873600000000))
SELECT n,
CASE
  WHEN length(n::text) >= 16 THEN to_timestamp(n/1000000.0)
  WHEN length(n::text) >= 13 THEN to_timestamp(n/1000.0)
  ELSE to_timestamp(n::double precision)
END AS ts
FROM v;

-- ローカル時刻を JST として timestamptz に上げる
SET TIME ZONE 'Asia/Tokyo';
SELECT ('294276-12-31 23:59:59.999999'::timestamp)::timestamptz; -- 環境によっては NG

-- 対策：範囲内であることを先に確認してから変換
WITH c(ts) AS (VALUES ('294276-12-31 23:59:59.999999'::timestamp))
SELECT CASE
  WHEN ts <= '294276-12-31 23:59:59.999999'::timestamp
  THEN ts::timestamptz
  ELSE NULL
END
FROM c;

-- 未来に大きく飛ばすとオーバーフロー
SELECT '2000-01-01'::timestamp + interval '500000000 days'; -- NG

-- 対策：演算前にクランプ（上限・下限で丸める）
WITH base(ts) AS (VALUES ('2000-01-01'::timestamp))
SELECT LEAST(
         GREATEST(ts + interval '100 years',
                  '0001-01-01'::timestamp),
         '2100-01-01'::timestamp
       ) AS safe_ts
FROM base;

-- "2025/08/22 23:59:59" を MM-DD と誤って読むと別日になるなど
SELECT to_timestamp('2025/08/22 23:59:59', 'YYYY/MM/DD HH24:MI:SS'); -- OK

-- TZ 付き文字列は timestamptz へ
SELECT to_timestamp('2025-08-22T23:59:59+09', 'YYYY-MM-DD"T"HH24:MI:SSOF')::timestamptz;

-- 事前バリデーション（パース可能かつ範囲内のみ変換）
WITH raw(s) AS (VALUES ('2025-08-22 23:59:59'), ('999999-01-01 00:00:00'))
SELECT s,
CASE
  WHEN s ~ '^\d{4}-\d{2}-\d{2} '  -- 簡易パターン例
   AND to_timestamp(s, 'YYYY-MM-DD HH24:MI:SS')
       BETWEEN '0001-01-01'::timestamp AND '2100-01-01'::timestamp
  THEN to_timestamp(s, 'YYYY-MM-DD HH24:MI:SS')
  ELSE NULL
END AS ts
FROM raw;

-- 受け入れテーブル側で CHECK を設ける
CREATE TABLE t_migrate(
  id bigint primary key,
  ts timestamp
  , CHECK (ts IS NULL OR ts >= '1900-01-01'::timestamp)
);

-- ETL 側で 0 日付を NULL に
INSERT INTO t_migrate(id, ts)
SELECT id,
       CASE WHEN src_date IN ('0000-00-00','0000-00-00 00:00:00') THEN NULL
            ELSE to_timestamp(src_epoch/1000.0) END
FROM ext;

SELECT 'infinity'::timestamp, '-infinity'::timestamp; -- OK

-- 期限切れ未設定を infinity に統一
CREATE TABLE licenses(
  user_id bigint primary key,
  expires_at timestamp NOT NULL DEFAULT 'infinity'
);

-- Bigint のエポック値を「秒/ミリ秒/マイクロ秒」自動判定して timestamp を返す
CREATE OR REPLACE FUNCTION safe_to_timestamp(v bigint)
RETURNS timestamp LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
    WHEN v IS NULL THEN NULL
    WHEN length(v::text) >= 16 THEN to_timestamp(v/1000000.0)
    WHEN length(v::text) >= 13 THEN to_timestamp(v/1000.0)
    ELSE to_timestamp(v::double precision)
  END
$$;

-- timestamptz 版（タイムゾーンポリシーに合わせて使用）
CREATE OR REPLACE FUNCTION safe_to_timestamptz(v bigint)
RETURNS timestamptz LANGUAGE sql IMMUTABLE AS $$
  SELECT (safe_to_timestamp(v))::timestamptz
$$;

-- 使用例
SELECT safe_to_timestamp(1692873600000), safe_to_timestamptz(1692873600);

-- 1) 実行中のタイムゾーンを確認
SHOW TIME ZONE;

-- 2) 文字列→タイムスタンプがどの型へ落ちるか確認
SELECT pg_typeof('2025-08-22 12:34:56'::timestamp), pg_typeof(('2025-08-22 12:34:56')::timestamptz);

-- 3) 端の値に触れていないかクイックチェック
SELECT
  MIN(my_ts) <  '0001-01-01'::timestamp AS has_too_small,
  MAX(my_ts) >  '2100-01-01'::timestamp AS has_too_large
FROM my_table;

-- 4) to_timestamp に渡す前に桁数と想定範囲を確認
WITH raw(v) AS (VALUES (999999999999999::bigint))
SELECT v,
       length(v::text) AS digits,
       CASE WHEN v BETWEEN 0 AND 32503680000 THEN 'within 3000-01-01'
            ELSE 'suspicious' END AS range_hint
FROM raw;

-- 例: ログ取り込み（秒/ミリ秒混在）を一括正規化し、範囲外は捨てる
INSERT INTO events(id, occurred_at)
SELECT id,
       CASE
         WHEN val IS NULL THEN NULL
         WHEN length(val::text) BETWEEN 9 AND 11 THEN to_timestamp(val::double precision)
         WHEN length(val::text) = 13       THEN to_timestamp(val/1000.0)
         WHEN length(val::text) BETWEEN 16 AND 17 THEN to_timestamp(val/1000000.0)
         ELSE NULL
       END AS occurred_at
FROM staging
WHERE
  -- 変換結果が安全範囲に収まるものだけ
  CASE
    WHEN length(val::text) BETWEEN 9 AND 11 THEN to_timestamp(val::double precision)
    WHEN length(val::text) = 13       THEN to_timestamp(val/1000.0)
    WHEN length(val::text) BETWEEN 16 AND 17 THEN to_timestamp(val/1000000.0)
  END
  BETWEEN '1970-01-01'::timestamp AND '2100-01-01'::timestamp;