概要
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;