SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Reading dates, times, and time zones safely
Tell PostgreSQL's date, timestamp, and timestamptz apart, convert between clock readings and instants with AT TIME ZONE, and parse text dates without guessing.
What you will learn
- Tell a timestamp (clock reading) from a timestamptz (absolute instant) on sight.
- Convert either direction with AT TIME ZONE and an IANA name like 'Europe/Berlin'.
- Parse ambiguous date text with TO_DATE format models instead of relying on DateStyle.
- Predict how the session TimeZone changes what EXTRACT returns from a timestamptz.
Understanding Reading dates, times, and time zones safely
PostgreSQL has three shapes of temporal value and the difference is not cosmetic. A date is a calendar day with no time and no zone. A timestamp (short for timestamp without time zone) is a calendar day plus a clock reading, which describes what some clock showed but does not identify a point on the world's timeline. A timestamptz (timestamp with time zone) is the opposite: a single absolute instant, held internally as a count of microseconds from an epoch in UTC, with no zone stored in the value at all despite the type name, so the offset you see printed is manufactured at output time by applying the session's TimeZone setting.
AT TIME ZONE is the bridge between those two worlds, and it runs in whichever direction the input type demands. Given a timestamptz it answers "what did a clock in that zone read at this instant" and hands back a zone-less timestamp; given a timestamp it answers "which instant was it when a clock in that zone read this" and hands back a timestamptz. That asymmetry is why applying it twice with the same zone returns you to the type and value you started from. Always name the zone the IANA way, such as 'America/Sao_Paulo', because abbreviations like EST are fixed offsets that know nothing about daylight saving and will be an hour wrong for part of the year.
Reading dates out of text is a separate hazard. A literal like '03/04/2024' has no single meaning, and a plain cast resolves it using DateStyle, a server setting that can differ between your laptop and production, so one statement can silently produce two different dates. TO_DATE and TO_TIMESTAMP take an explicit format model and therefore mean the same thing on every server, but they are lenient rather than strict: out-of-range fields roll over instead of raising. When you control the producer, ISO 8601 text is safest, since '2024-04-03' and '2024-04-03T12:00:00Z' parse identically under any DateStyle.
SET TimeZone = 'UTC';
SELECT '2024-11-05 23:30:00+00'::timestamptz AS stored_instant,
'2024-11-05 23:30:00+00'::timestamptz AT TIME ZONE 'Asia/Tokyo' AS tokyo_clock,
'2024-11-05 23:30:00'::timestamp AT TIME ZONE 'Asia/Tokyo' AS tokyo_2330_utc;A timestamptz is an instant with no zone stored inside it and a timestamp is a clock reading with no instant, so every move between them must name a zone.
Worked examples
Format model beats DateStyle
Shows that the same date string means different things to a cast and to TO_DATE.
SET DateStyle = 'ISO, DMY';
SELECT '03/04/2024'::date AS cast_under_dmy,
TO_DATE('03/04/2024', 'MM/DD/YYYY') AS explicit_month_first;Example explained
Line 1SET DateStyle = 'ISO, DMY' tells the parser that bare slash-separated literals are day-first; the string itself did not change.
Line 2The cast therefore reads 3 April; on a server configured MDY the identical statement returns 4 March instead, with no warning.
Line 3TO_DATE ignores DateStyle: the model 'MM/DD/YYYY' pins the first field to the month, so it returns 2024-03-04 everywhere.
Line 4The ISO part of DateStyle only controls output formatting, so both values print as YYYY-MM-DD regardless of how they were parsed.
EXTRACT reads through the session zone
Demonstrates that pulling a field out of a timestamptz depends on a setting, not on the stored value.
SET TimeZone = 'UTC';
SELECT EXTRACT(DAY FROM '2024-11-06 02:30:00+00'::timestamptz) AS utc_day;
SET TimeZone = 'America/New_York';
SELECT EXTRACT(DAY FROM '2024-11-06 02:30:00+00'::timestamptz) AS ny_day;Example explained
Line 1The stored value never changes: 2024-11-06 02:30 UTC is one instant, and neither SET statement touches it.
Line 2Under TimeZone = 'UTC' that instant renders as 2024-11-06 02:30, so the day field is 6.
Line 3Under TimeZone = 'America/New_York' the same instant renders as 2024-11-05 21:30, so the day field is 5.
Line 4EXTRACT (and its function form date_part) takes no zone argument, so the fix belongs on the input: EXTRACT(DAY FROM ts AT TIME ZONE 'America/New_York').
Clock readings that are missing or doubled
Shows what happens when a wall-clock string falls in a daylight saving gap or repeat.
SET TimeZone = 'UTC';
SELECT '2024-03-10 02:30:00'::timestamp AT TIME ZONE 'America/New_York' AS gap_time,
'2024-11-03 01:30:00'::timestamp AT TIME ZONE 'America/New_York' AS ambiguous_time;Example explained
Line 1Printing in UTC lets you read the resulting instants directly instead of decoding an offset suffix.
Line 202:30 on 10 March never occurs in New York because the clock jumps from 02:00 to 03:00, so PostgreSQL applies the offset in force before the jump (-05) and produces 07:30 UTC, which is 03:30 local.
Line 301:30 on 3 November occurs twice; PostgreSQL again uses the pre-transition offset (-04) and so picks the first occurrence, 05:30 UTC, not 06:30 UTC.
Line 4Neither case raises an error, so wall-clock text collected on those dates converts to an instant you may not have intended.
Important notes
TO_DATE does not validate its input: to_date('2024-02-31', 'YYYY-MM-DD') returns 2024-03-02, while '2024-02-31'::date raises a date/time field out of range error. Use the cast when bad input should be rejected.
Other engines split this differently: MySQL's DATETIME is zone-less while its TIMESTAMP converts through the session zone, and SQL Server's datetimeoffset stores a fixed offset rather than an IANA zone, so daylight saving stays your problem.
Common mistakes
Loading '2024-11-05 23:30:00+09' into a timestamp column: the +09 is parsed and then thrown away, so the row records 23:30, the true instant is nine hours off, and nothing remains to recover it from.
Writing AT TIME ZONE 'EST' or 'PST': PostgreSQL treats those as fixed offsets, so summer dates land an hour off, whereas 'America/New_York' applies whichever rule was in force on that date.
Grouping by EXTRACT(DAY FROM created_at) on a timestamptz and calling it the local day: the buckets shift with the session TimeZone, so the same report gives different daily counts from a different client.
Try it yourself
Change, predict, then run
In a Postgres editor run SET TimeZone = 'America/New_York', then select '2024-03-10 06:30:00+00'::timestamptz and '2024-03-10 07:30:00+00'::timestamptz side by side. Explain why one prints an offset of -05 and the other -04 even though they are an hour apart.
Open the SQL workspaceCheck your understanding
A column is declared timestamp with time zone. From a session where TimeZone is 'Asia/Tokyo' you insert the literal '2024-06-01 09:00:00+02'. What does the stored value actually contain?
- The literal text '2024-06-01 09:00:00+02', preserved so it can be printed back unchanged
- 09:00 together with the zone Asia/Tokyo, because the session zone overrides the offset in the literal
- The single instant 2024-06-01 07:00 UTC, with no zone or offset kept alongside it
- The instant 2024-06-01 07:00 UTC plus the +02 offset, so the value always prints as +02
Show answer
The +02 is used exactly once, to work out which instant was meant, and is then discarded; a timestamptz holds only that instant. The last option is tempting because the type is spelled "with time zone", but nothing zone-specific survives the insert: the session TimeZone decides only how the instant is rendered on the way out, so the same row reads 16:00+09 in Tokyo and 07:00+00 in UTC. The session zone also never overrides an offset that the literal states explicitly; it would only fill in for a literal with no offset at all.