JSON日付文字列をSQL DATEおよびTIMESTAMPカラムに変換する

日付とタイムスタンプのマッピング

JSONにはネイティブの日付型がなく、日付は通常ISO 8601形式の文字列としてエンコードされます。コンバーターは日付らしき文字列を検出し、適切なSQL時間型にマッピングします。

JSON例

{
  "id": 1,
  "title": "Project Alpha",
  "start_date": "2024-06-15",
  "created_at": "2024-06-15T10:30:00Z",
  "updated_at": "2024-06-15T10:30:00.123456Z",
  "deadline": "2024-12-31T23:59:59+09:00"
}

生成されるSQL

CREATE TABLE projects (
  id INTEGER NOT NULL,
  title VARCHAR(255) NOT NULL,
  start_date DATE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP(6) WITH TIME ZONE NOT NULL,
  deadline TIMESTAMP WITH TIME ZONE NOT NULL
);

検出ヒューリスティック

コンバーターは以下の方法で日付文字列を識別します：

1. ISO 8601パターン — YYYY-MM-DD、YYYY-MM-DDTHH:mm:ssZ など。
2. カラム名のヒント — _at、_date、_time、created、updated、deleted、expiresを含む名前。
3. Unixタイムスタンプ — timestamp、created_atなどの名前を持つカラムの大きな整数（>1,000,000,000）。

SQL型の選択

パターン	SQL型
2024-06-15（日付のみ）	DATE
2024-06-15T10:30:00（tzなし）	TIMESTAMP / DATETIME
2024-06-15T10:30:00Z（tzあり）	TIMESTAMP WITH TIME ZONE
小数秒	マイクロ秒精度のTIMESTAMP(6)

タイムゾーンのベストプラクティス

常にタイムゾーン情報付きでタイムスタンプを保存してください。 TIMESTAMP WITH TIME ZONE（PostgreSQLのTIMESTAMPTZ）を使用すると、保存時にUTCに正規化され、取得時にセッションのタイムゾーンに変換されます。タイムゾーンなしで保存すると、アプリケーションが複数のリージョンにまたがる場合に曖昧さが生じます。

自動生成タイムスタンプ

created_atとupdated_atカラムに対して、コンバーターは以下を追加します：

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()

行の変更ごとにupdated_atを更新するトリガーまたはアプリケーションレベルのロジックも追加すべきです。

データベースの違い

• PostgreSQL: TIMESTAMPTZはTIMESTAMP WITH TIME ZONEのエイリアスです。
• MySQL: DATETIMEはタイムゾーンなしで値を保存します。TIMESTAMPはUTCに変換しますが、範囲が制限されています（2038年まで）。
• SQL Server: DATETIMEOFFSETはタイムゾーンオフセットを明示的に保存します。