Converting JSON Date Strings to SQL Date and Timestamp Values

JSON Dates to SQL Temporal Types

JSON has no native date type, so dates are encoded as strings (usually ISO 8601) or as Unix timestamps (integers). Converting these to SQL requires understanding the target column type and database dialect.

Example JSON

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

PostgreSQL Output

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 Output

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

Date Format Mapping

JSON format	SQL type	Example
"2024-06-15T09:30:00Z"	TIMESTAMP / TIMESTAMPTZ	ISO 8601 with timezone
"2024-06-15"	DATE	Date only
"09:30:00"	TIME	Time only
1718444400	TIMESTAMP (via conversion)	Unix epoch seconds
1718444400000	TIMESTAMP (via conversion)	Unix epoch milliseconds

Timezone Handling

ISO 8601 strings may include timezone offsets:

• "2024-06-15T09:30:00Z" -- UTC
• "2024-06-15T09:30:00+05:30" -- IST offset
• "2024-06-15T09:30:00" -- No timezone (ambiguous)

PostgreSQL's TIMESTAMPTZ stores UTC and converts on retrieval. MySQL's DATETIME does not store timezone information, so the converter should normalize to UTC before insertion.

Unix Timestamp Detection

The converter can auto-detect Unix timestamps by checking if a numeric value falls within a reasonable epoch range (e.g., 1000000000 to 9999999999 for seconds, or 10^12 to 10^15 for milliseconds).

Best Practice

Always store dates in UTC in the database and convert to local time in the application layer. This avoids ambiguity and makes cross-timezone queries reliable.