Dates: Advanced

Convert times across global time zones

SQL has two timestamp types: TIMESTAMP (no timezone) and TIMESTAMP WITH TIME ZONE. The latter stores UTC internally but displays in your session timezone.

Use AT TIME ZONE to convert timestamps between timezones.

SELECT
  event_timestamp ___ ___ AS pacific_time
FROM event_data

AT TIME ZONE preserves the absolute moment in time while changing only the display offset. The underlying UTC value never changes, making conversions fully reversible and safe for calculations.

Snap timestamps to clean boundaries

DATE_TRUNC rounds timestamps down to the start of a unit (day, week, month, year). This is essential for grouping by time periods.

DATE_TRUNC sets all smaller units to their minimum value. Truncating to month sets day to 1 and time to 00:00:00.

Combine DATE_TRUNC with GROUP BY to create time-series aggregations.

Weeks can start on Sunday or Monday depending on locale. DATE_TRUNC('week', ...) typically uses Monday. Check your database documentation.

SELECT
  ___(___, order_date) AS week,
  ___ AS order_count
FROM orders
GROUP BY DATE_TRUNC('week', order_date)

DATE_TRUNC is preferred over EXTRACT for grouping because the truncated value is a valid timestamp that sorts and displays naturally. EXTRACT returns a plain integer that loses date context.

For fiscal calendars that do not align with standard weeks or months, DATE_TRUNC to day followed by custom interval arithmetic provides the necessary flexibility.

Read and write dates in any format

Use DATE_PARSE to convert formatted strings into timestamps.

Use DATE_FORMAT to convert timestamps into formatted strings for display or export.

SELECT
  ___(
    order_date,
    ___
    ) AS formatted
FROM orders

DATE_FORMAT output is a string, not a timestamp. Once formatted, the value cannot be used for date arithmetic or comparisons without parsing it back. Format only at the final output stage.

Always store timestamps in native date types and use DATE_FORMAT exclusively in SELECT for display. Storing pre-formatted strings prevents any future date calculations.

Work with standard date representations

FROM_ISO8601_TIMESTAMP parses ISO 8601 strings to timestamps. TO_ISO8601 formats timestamps as ISO 8601 strings. These specialized functions handle the nuances of the standard, including the 'T' separator and timezone suffixes.

FROM_ISO8601_TIMESTAMP handles timezone offsets automatically, normalizing to UTC. The string '2024-03-15T10:00:00-04:00' (10 AM Eastern, 4 hours behind UTC) becomes 14:00 UTC.

REST APIs

Parse incoming timestamps and format outgoing responses

Data exchange

Universal format for cross-system data transfer

Event logging

Standard format for audit trails and log files

SELECT
  ___(
    ___
    ) AS parsed_timestamp
FROM api_calls

FROM_ISO8601_TIMESTAMP normalizes all timezone offsets to UTC automatically. After parsing, every timestamp is comparable regardless of the original offset in the API string.

ISO 8601 parsing is stricter than DATE_PARSE. It expects exact compliance with the standard format, so malformed strings from unreliable sources should be validated before parsing.

Pairing FROM_ISO8601_TIMESTAMP with TO_ISO8601 creates a clean round-trip: parse incoming API timestamps to UTC, do all calculations natively, then format back for the response.

Display dates exactly how users expect

DATE_FORMAT converts timestamps to formatted strings for export, display, or downstream systems. While PARSE_DATETIME brings data in, DATE_FORMAT sends it out in exactly the format consumers require.

For API responses and file exports, use TO_ISO8601 or DATE_FORMAT with ISO patterns:

SELECT
  order_id,
  ___(___) AS api_timestamp
FROM orders

TO_ISO8601 always outputs the Z suffix indicating UTC. If your database stores timestamps in a non-UTC timezone, convert to UTC first using AT TIME ZONE before calling TO_ISO8601.

The millisecond precision in TO_ISO8601 output (.000Z) is part of the standard. Consuming systems should handle it, but if a downstream API rejects milliseconds, use DATE_FORMAT with a custom pattern instead.