Convert JSON Date Strings to SQL DATE and TIMESTAMP Columns

Date and Timestamp Mapping

JSON has no native date type — dates are encoded as strings, typically in ISO 8601 format. The converter detects date-like strings and maps them to the appropriate SQL temporal type.

Example 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"
}

Generated 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
);

Detection Heuristics

The converter identifies date strings by:

1. ISO 8601 patterns — YYYY-MM-DD, YYYY-MM-DDTHH:mm:ssZ, etc.
2. Column name hints — Names containing _at, _date, _time, created, updated, deleted, expires.
3. Unix timestamps — Large integers (>1_000_000_000) in columns named timestamp, created_at, etc.

SQL Type Selection

Pattern	SQL type
2024-06-15 (date only)	DATE
2024-06-15T10:30:00 (no tz)	TIMESTAMP / DATETIME
2024-06-15T10:30:00Z (with tz)	TIMESTAMP WITH TIME ZONE
Fractional seconds	TIMESTAMP(6) for microsecond precision

Timezone Best Practice

Always store timestamps with timezone information. Use TIMESTAMP WITH TIME ZONE (PostgreSQL TIMESTAMPTZ) so that values are normalized to UTC on storage and converted to the session timezone on retrieval. Storing without timezone leads to ambiguity when your application spans multiple regions.

Auto-Generated Timestamps

For created_at and updated_at columns, the converter adds:

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

You should also add a trigger or application-level logic to update updated_at on every row modification.

Database Differences

• PostgreSQL: TIMESTAMPTZ is an alias for TIMESTAMP WITH TIME ZONE.
• MySQL: DATETIME stores values without timezone; TIMESTAMP converts to UTC but has a limited range (up to 2038).
• SQL Server: DATETIMEOFFSET stores timezone offset explicitly.