Brightlane's SLA monitoring system measures elapsed time between two events recorded in different time zones. The first event was logged at 09:00 UTC and the second at 18:00 Indian Standard Time (UTC+05:30) on the same calendar day, '2024-03-15'.
Write a query to return the true elapsed time between the two timezone-aware timestamps '2024-03-15 09:00:00+00' and '2024-03-15 18:00:00+05:30'.
Output:
- A single row with one column,
elapsed, typed as an interval.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
'2024-03-15 18:00:00+05:30'::TIMESTAMPTZ - '2024-03-15 09:00:00+00'::TIMESTAMPTZ AS elapsedThe shape
Subtracting two TIMESTAMPTZ values returns the true elapsed time between them, regardless of the time zones the values were originally written in. PostgreSQL converts both sides to UTC before subtracting, so the result reflects absolute time, not local clock time. The interval between 09:00 UTC and 18:00 IST on March 15, 2024 is three hours and thirty minutes, because IST is UTC+05:30.
Clause by clause
SELECT '2024-03-15 18:00:00+05:30'::timestamptz - '2024-03-15 09:00:00+00'::timestamptz AS elapsedcasts both literals toTIMESTAMPTZand subtracts the earlier moment from the later. The first literal carries a+05:30offset; PostgreSQL normalizes it to 12:30 UTC. The second literal is already UTC at 09:00. The subtraction runs on the two UTC values and returns03:30:00. The aliasAS elapsednames the duration column.
Why TIMESTAMPTZ and not TIMESTAMP
TIMESTAMP strips the time zone before storage and treats the literal as a wall-clock value. Subtracting '2024-03-15 18:00:00'::timestamp - '2024-03-15 09:00:00'::timestamp would return 9 hours, because the database has no record of the originating zones and reads both values as if they were in the same place. The difference is silent. The numeric result looks plausible but represents wall-clock subtraction across two different time zones, which has no defined meaning. TIMESTAMPTZ is the type that preserves the absolute-time semantics this calculation requires.
The trap
The most dangerous mistake on a problem like this is storing events from multiple time zones as TIMESTAMP and only realizing the issue when an SLA report shows impossible durations. The time zone offset is discarded at write time for TIMESTAMP and cannot be recovered. Once the values are in the table as bare timestamps, the originating zone is gone. The defense is at the schema level: any event timeline that spans regions should use TIMESTAMPTZ so the zone information is preserved through the conversion to UTC and the subtraction returns the true elapsed time.
You practiced TIMESTAMPTZ - TIMESTAMPTZ — PostgreSQL converts both values to UTC before subtracting, so the resulting interval reflects true elapsed time regardless of the originating zones.
