Truncate Timestamp to Date — Date and Time Types in SQL

The problem

Brightlane's reporting system needs to extract the calendar date from a full timestamp value so daily reports group correctly.

Write a query to return the string '2024-06-15 23:45:00' first cast as a timezone-naive timestamp and then cast as a calendar date.

Output:

A single row with one column, report_date, typed as a date.

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 23:45:00'::TIMESTAMP::date AS report_date

The shape

'2024-06-15 23:45:00'::timestamp::date runs two casts on one literal: the first lands the value as a TIMESTAMP, the second drops the time-of-day and leaves only the calendar date. The chained cast is how a reporting query rolls a full timestamp up to the day it belongs to.

Clause by clause

SELECT '2024-06-15 23:45:00'::timestamp::date AS report_date evaluates the literal once and applies two casts in left-to-right order. The first ::timestamp resolves the string as a TIMESTAMP of 2024-06-15 23:45:00. The second ::date truncates that timestamp to its calendar component, returning 2024-06-15. The final output column is DATE, which is what daily reports group on.
There is no FROM because no table is being read.

Why two casts and not just `::date` directly

'2024-06-15 23:45:00'::date would also produce 2024-06-15 here, because PostgreSQL can parse the date portion of the string and discard the time when casting straight to DATE. The chained form is the canonical shape because it documents the two steps explicitly: first establish the value is a TIMESTAMP, then drop the time. When the input arrives as a real TIMESTAMP column rather than a literal, the second cast is the only one that does any work, and writing both makes the intent unambiguous.

The trap

The grader checks the final column's type, not the intermediate steps. The destination here is DATE, and any cast chain that lands there passes. What does not pass is leaving the cast off and submitting a bare timestamp literal: the time-of-day stays attached, the column type is wrong, and the daily aggregation downstream gets one row per second instead of one row per day. The ::date cast on the final step is what makes the report's grouping correct.

You practiced chained casting TIMESTAMP-to-DATE — the time-of-day component is dropped, leaving the calendar date.

Return the string `'2024-06-15 23:45:00'` first cast as a timezone-naive timestamp and then cast as a calendar date

The shape

Clause by clause

Why two casts and not just `::date` directly

The trap

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

The shape

Clause by clause

Why two casts and not just ::date directly

The trap

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

Why two casts and not just `::date` directly