Temporal Types & DST
Concepts covered: Data Types
In the subscriptions table, started_at is an instant that happened, next_renewal_at is an appointment that will happen, and trial_ends_at is a calendar day. In payment_events, occurred_at records each charge attempt, another instant. A type that fits one kind of time corrupts the others.
The temporal types and what one value means
- DATE: a calendar day with no clock and no zone. 2026-11-01 is a different span of instants in Tokyo and in Denver.
- TIME: a wall-clock time with no day. 09:00 is an appointment pattern, and it names an instant only once a day and a zone are attached.
- TIMESTAMP (naive): a day and a clock reading with no zone. 2026-11-01 01:30:00 could be any of roughly 38 instants, one per UTC offset in use.
- TIMESTAMPTZ: an instant on the global timeline, converted to UTC on write. The same instant reads back in any zone the session asks for.
- INTERVAL: a duration. '1 month' is calendar arithmetic; '2592000 seconds' is 30 days elapsed, and the two disagree in every month that is not 30 days long.
The hour that happens twice
On 2026-11-01 the United States leaves daylight saving time: at 02:00 Eastern Daylight Time the clocks return to 01:00 Eastern Standard Time, so every reading between 01:00 and 02:00 occurs twice. On 2026-03-08 the readings between 02:00 and 03:00 never occur at all. A naive TIMESTAMP payment_events.occurred_at filled by a server set to America/New_York records 2 charge attempts an hour apart as the same value.
| Wall clock in New York | Actual instant (UTC) | Minutes since 00:30 local |
|---|---|---|
| 2026-11-01 00:30:00 | 2026-11-01 04:30:00Z | 0 |
| 2026-11-01 01:30:00 (first time) | 2026-11-01 05:30:00Z | 60 |
| 2026-11-01 01:30:00 (second time) | 2026-11-01 06:30:00Z | 120 |
| 2026-11-01 02:30:00 | 2026-11-01 07:30:00Z | 180 |
In a naive column the second and third rows are indistinguishable, and elapsed time between them computes as 0 minutes for a gap of 60. As TIMESTAMPTZ they hold 05:30Z and 06:30Z, and the subtraction is correct in every zone. The point reaches past DST: subtracting 2 instants gives elapsed time, while subtracting 2 wall-clock readings is right only within one zone on a day without a transition.
PostgreSQL's TIMESTAMPTZ stores the UTC instant and discards the offset it arrived with: '2026-11-01 01:30:00-05:00' reads back in the session's TimeZone, and the Eastern origin is gone. Where the originating zone is a business fact (which local day a charge belongs to on a statement), it gets its own column, occurred_tz VARCHAR(40) holding an IANA name such as America/New_York.
Past instants versus future appointments
A charge that already happened is an instant and belongs in TIMESTAMPTZ. A renewal at 09:00 local on the first of the month is an appointment, and converting it to a UTC instant today bakes in today's timezone rules. Mexico abolished daylight saving time in October 2022, so a Mexico City renewal stored as a UTC instant in 2021 fires an hour early from 2023 on; Egypt reinstated DST in April 2023 and shifted pre-computed instants the other way. The schema that survives law changes stores renewal_time TIME plus renewal_tz holding the IANA name, and computes the instant when the scheduler enqueues the job.
Durations split the same way. A 14-day trial is elapsed time and stores as trial_days INT. A monthly plan is calendar time: in PostgreSQL started_at + INTERVAL '1 month' from January 31 lands on February 28, and another month lands on March 28, so a subscription begun on the 31st drifts to the 28th forever unless billing_day_of_month INT is stored separately. Store the rule the business stated, and derive each date from it at the point of use.
| Platform | Naive civil time | Instant | Note |
|---|---|---|---|
| PostgreSQL | TIMESTAMP | TIMESTAMPTZ | Instant type keeps UTC and drops the offset |
| MySQL | DATETIME | TIMESTAMP | TIMESTAMP range ends 2038-01-19 03:14:07 UTC |
| Snowflake | TIMESTAMP_NTZ | TIMESTAMP_TZ keeps offset; TIMESTAMP_LTZ renders in session zone | Bare TIMESTAMP is an alias for NTZ by default |
| BigQuery | DATETIME | TIMESTAMP | A literal with no zone is read as UTC |
A subscription renews at 09:00 on the 1st of each month in the customer's own timezone, and the charge job must run at that moment. Which columns hold the schedule?
- next_renewal_at TIMESTAMPTZ, with the next 12 instants pre-computed on signup
- renewal_time TIME plus renewal_tz as an IANA zone name, with the instant computed when the job is scheduled
- renewal_at TIMESTAMP naive plus utc_offset VARCHAR(6) holding '-05:00'
- renewal_day INT and run every job at 09:00 UTC
About this lesson
This section is part of the Schema Types lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.