概要

timestamptz型に取り込める文字列のパターンについて「これ、いけるんだっけ？」とかやってることが多いので、まとめました。

SQL実行

何も考えずに文字列::timestamptzとかやると例外で落ちるので、変換試みてダメならnullを返す関数を作りました。その後、各パターンにて変換テストしてます。変換不能パターンは当たり前過ぎてあんまり面白くないけど、一応。

-- text→timestamptz型に変換を試みて、例外ならnullを返す関数
CREATE OR REPLACE FUNCTION f_try_to_timestamptz(text) RETURNS timestamptz AS $$
BEGIN
    RETURN $1::timestamptz;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 変換テスト
WITH test_data AS (
  SELECT unnest(ARRAY[
    -- 変換可能なパターン
    '2023-04-01 09:46:00.000000', -- ミリ秒6桁0埋め
    '2023-04-01 09:46:00.123456', -- ミリ秒6桁
    '2023-04-01 09:46:00.123', -- ミリ秒3桁
    '2023-04-01 09:46:00.0', -- ミリ秒1桁
    '2023-04-01 09:46:00', -- ミリ秒なし
    '2023-4-1 9:6:3', -- 0埋めなし
    '2023/04/01 09:46:00', -- スラッシュ区切り
    '2023-04-01 09:46:00+09:00', -- タイムゾーン付き
    '2023-04-01 09:46:00Z', -- UTC指定
    '2023-04-01 02:46:00 PM', -- 12時間表記
    '2023-04-01T09:46:00.123456+09:00', -- ISO 8601形式
    
    -- 変換不可能なパターン
    '2023-13-01 09:46:00', -- 不正な月
    '2023-04-31 09:46:00', -- 不正な日（4月は30日まで）
    '2023-04-01 25:46:00', -- 不正な時間
    '2023-04-01 09:60:00', -- 不正な分
    '2023-04-01 09:46:61', -- 不正な秒
    '2023/04/01 09.46.00', -- 不正な時刻区切り文字
    'Invalid DateTime', -- 完全な不正文字列
    '20230401094600', -- 区切り文字なし
    '2023年04月01日 09時46分00秒' -- 日本語形式
  ]) AS datetime_str
)
SELECT
  datetime_str,
  CASE 
    WHEN f_try_to_timestamptz(datetime_str) IS NOT NULL THEN '変換可能'
    ELSE '変換不可'
  END AS conversion_status,
  f_try_to_timestamptz(datetime_str) AS converted_timestamp,
  CASE 
    WHEN f_try_to_timestamptz(datetime_str) IS NOT NULL THEN pg_typeof(f_try_to_timestamptz(datetime_str))::text
    ELSE NULL
  END AS result_type
FROM test_data
ORDER BY -- 変換できたパターンを上に持ってくる並び替え
  CASE WHEN f_try_to_timestamptz(datetime_str) IS NOT NULL THEN 0 ELSE 1 END,
  datetime_str;

実行結果

datetime_str	conversion_status	converted_timestamp	result_type
2023-04-01 02:46:00 PM	変換可能	2023-04-01 14:46:00.000 +0900	timestamp with time zone
2023-04-01 09:46:00	変換可能	2023-04-01 09:46:00.000 +0900	timestamp with time zone
2023-04-01 09:46:00+09:00	変換可能	2023-04-01 09:46:00.000 +0900	timestamp with time zone
2023-04-01 09:46:00.0	変換可能	2023-04-01 09:46:00.000 +0900	timestamp with time zone
2023-04-01 09:46:00.000000	変換可能	2023-04-01 09:46:00.000 +0900	timestamp with time zone
2023-04-01 09:46:00.123	変換可能	2023-04-01 09:46:00.123 +0900	timestamp with time zone
2023-04-01 09:46:00.123456	変換可能	2023-04-01 09:46:00.123 +0900	timestamp with time zone
2023-04-01 09:46:00Z	変換可能	2023-04-01 18:46:00.000 +0900	timestamp with time zone
2023-04-01T09:46:00.123456+09:00	変換可能	2023-04-01 09:46:00.123 +0900	timestamp with time zone
2023-4-1 9:6:3	変換可能	2023-04-01 09:06:03.000 +0900	timestamp with time zone
2023/04/01 09:46:00	変換可能	2023-04-01 09:46:00.000 +0900	timestamp with time zone
2023-04-01 09:46:61	変換不可
2023-04-01 09:60:00	変換不可
2023-04-01 25:46:00	変換不可
2023-04-31 09:46:00	変換不可
2023-13-01 09:46:00	変換不可
2023/04/01 09.46.00	変換不可
20230401094600	変換不可
2023年04月01日 09時46分00秒	変換不可
Invalid DateTime	変換不可

あとしまつ

関数の削除を行うSQL。

DROP FUNCTION f_try_to_timestamptz;