JSON日付文字列をSQL DATE・TIMESTAMP値に変換する

JSON日付からSQL時間型へ

JSONにはネイティブの日付型がないため、日付は文字列（通常ISO 8601）またはUnixタイムスタンプ（整数）としてエンコードされます。SQLへの変換には、ターゲットカラムの型とデータベース方言の理解が必要です。

JSONの例

{
  "id": 1,
  "event": "deployment",
  "created_at": "2024-06-15T09:30:00Z",
  "scheduled_for": "2024-07-01",
  "updated_at": 1718444400
}

PostgreSQL出力

INSERT INTO events (id, event, created_at, scheduled_for, updated_at) VALUES
  (1, 'deployment', '2024-06-15T09:30:00Z'::TIMESTAMPTZ,
   '2024-07-01'::DATE,
   TO_TIMESTAMP(1718444400));

MySQL出力

INSERT INTO events (id, event, created_at, scheduled_for, updated_at) VALUES
  (1, 'deployment', '2024-06-15 09:30:00',
   '2024-07-01',
   FROM_UNIXTIME(1718444400));

日付フォーマットマッピング

JSONフォーマット	SQL型	例
"2024-06-15T09:30:00Z"	TIMESTAMP / TIMESTAMPTZ	タイムゾーン付きISO 8601
"2024-06-15"	DATE	日付のみ
"09:30:00"	TIME	時間のみ
1718444400	TIMESTAMP（変換経由）	Unixエポック秒
1718444400000	TIMESTAMP（変換経由）	Unixエポックミリ秒

タイムゾーン処理

ISO 8601文字列にはタイムゾーンオフセットが含まれる場合があります：

• "2024-06-15T09:30:00Z" -- UTC
• "2024-06-15T09:30:00+05:30" -- ISTオフセット
• "2024-06-15T09:30:00" -- タイムゾーンなし（曖昧）

PostgreSQLのTIMESTAMPTZはUTCで保存し、取得時に変換します。MySQLのDATETIMEはタイムゾーン情報を保存しないため、コンバーターは挿入前にUTCに正規化する必要があります。

Unixタイムスタンプの検出

コンバーターは、数値が合理的なエポック範囲（秒の場合は1000000000〜9999999999、ミリ秒の場合は10^12〜10^15）に収まるかチェックすることで、Unixタイムスタンプを自動検出できます。

ベストプラクティス

データベースでは常にUTCで日付を保存し、アプリケーション層でローカル時間に変換してください。これにより曖昧さが回避され、タイムゾーンをまたぐクエリの信頼性が向上します。