Compare Date Timestamp and Timestamptz — Date and Time Types in SQL

The problem

Brightlane's data quality report displays the same business event stored in three different formats so engineers can compare how each type renders.

Write a query to return three columns in a single row: the string '2024-06-15' cast as a calendar date; '2024-06-15 09:00:00' cast as a timezone-naive timestamp; and '2024-06-15 09:00:00+05:30' cast as a timezone-aware timestamp.

Output:

A single row with columns just_date, local_ts, and absolute_ts.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  '2024-06-15'::date AS just_date,
  '2024-06-15 09:00:00'::TIMESTAMP AS local_ts,
  '2024-06-15 09:00:00+05:30'::TIMESTAMPTZ AS absolute_ts

The shape

Three casts side by side put the same string-form value into three distinct types — DATE, TIMESTAMP, and TIMESTAMPTZ — so the engineers can read off how each type stores its input. Each cast is the only thing that distinguishes one column from the next; the literals themselves are nearly identical.

Clause by clause

SELECT '2024-06-15'::date AS just_date resolves the calendar-only string to DATE. No time-of-day, no zone.
'2024-06-15 09:00:00'::timestamp AS local_ts resolves the wall-clock string to TIMESTAMP. The value is stored as written; no zone information is attached or inferred.
'2024-06-15 09:00:00+05:30'::timestamptz AS absolute_ts resolves the offset-bearing string to TIMESTAMPTZ. PostgreSQL reads the +05:30, converts the wall-clock reading to UTC at parse time, and stores the absolute moment that 09:00 in India corresponds to.
There is no FROM because the values come directly from literals.

Why the explicit casts on every column

The grader checks each output column's type, not just its displayed value. '2024-06-15' looks like a date and '2024-06-15 09:00:00' looks like a timestamp, but both are text until a cast resolves them. Each column on the data-quality report has to land in the specific type it represents, and the ::date / ::timestamp / ::timestamptz casts are what make that happen. The destination type is the contract; the cast is what fulfills it.

The trap

The TIMESTAMP and TIMESTAMPTZ columns look interchangeable because their input strings differ only by the trailing offset. They are not interchangeable. local_ts records the clock reading with no zone; absolute_ts records a specific moment in UTC. The same business event written into one and then the other would compare unequal as soon as the session zone shifted. Choosing between the two is a semantic decision about whether the data represents a moment or a clock reading, and the cast is where that decision lands.

You practiced casting the same business moment into DATE, TIMESTAMP, and TIMESTAMPTZ side by side — three distinct types with three different storage semantics.

Return three columns in a single row: the string `'2024-06-15'` cast as a calendar date; `'2024-06-15 09:00:00'` cast as a timezone-naive timestamp; and `'2024-06-15 09:00:00+05:30'` cast as a timezone-aware timestamp

The shape

Clause by clause

Why the explicit casts on every column

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.