Brightlane's distributed transaction system records events with their UTC offset, and the engineering team is verifying how an offset-bearing literal parses.
Write a query to return the string '2024-06-15 14:30:00+00' cast as a timezone-aware timestamp.
Output:
- A single row with one column,
event_time, typed as a timezone-aware timestamp.
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-06-15 14:30:00+00'::TIMESTAMPTZ AS event_timeThe shape
The ::timestamptz cast resolves the literal as a timezone-aware TIMESTAMPTZ. PostgreSQL reads the +00 offset, converts the wall-clock value to UTC at parse time, and stores the absolute moment. That is the right type for a distributed transaction system that records events from sources in multiple zones.
Clause by clause
SELECT '2024-06-15 14:30:00+00'::timestamptz AS event_timeevaluates the literal once and returns it as a singleTIMESTAMPTZvalue. The+00inside the string tells PostgreSQL which offset to convert from; the::timestamptzcast tells it the destination type. Together they fix the absolute moment and the column type at the same time.- There is no
FROMbecause no table is being read.
The trap
Drop the cast and the literal stays text. The displayed value still looks like a timestamp, but the column's type is wrong and any downstream comparison against a real TIMESTAMPTZ column will either error or coerce in ways that depend on session settings. The ::timestamptz cast is what guarantees the result column is the type the engineering team is verifying against.
You practiced casting an offset-bearing literal to TIMESTAMPTZ — the type converts the value to UTC at write time and back to the session zone on read.
