A timestamp without time zone, also called a naive timestamp, stores a calendar date and clock time but no time-zone information. PostgreSQL uses this type by default when a column is declared simply as TIMESTAMP. The value therefore does not identify an absolute moment in time. section 3.1
A ticket duration that looks plausible but is wrong
Suppose a support ticket opens at 16:00 Pacific time and closes at 09:00 Greenwich Mean Time the next day. Subtracting the stored naive values produces an interval of 17 hours, even though the elapsed time is 10 hours. The subtraction compares clock readings while ignoring their different offsets. Converting both values to a single local zone avoids that particular mismatch, but daylight-saving transitions can still make local timestamps ambiguous or misleading. Store moments in time with TIMESTAMP WITH TIME ZONE instead, then convert them for display when necessary. section 3.1 section 3.9
SELECT pg_typeof(closed_at - opened_at), closed_at - opened_at FROM support.tickets WHERE id = 132591;
Inspect the result type and interval produced by subtracting the ticket’s opened_at and closed_at values. section 3.1
Treat naive timestamps as local labels rather than reliable event times. They are especially dangerous for auditing, duration calculations, and migrations: other database systems may expose similarly named date-time types with different precision or semantics, and converting them later cannot recover time-zone information that was never stored. section 10.5 section 3.9
