Truncate to Year and Day — DATE_TRUNC and EXTRACT in SQL

The problem

Brightlane's pipeline audit log records both the year boundary and the day boundary for each event timestamp to support period comparisons at multiple granularities.

Write a query to return both the year truncation and the day truncation of the timestamp '2024-08-22 09:15:00' in a single row.

Output:

A single row with columns year_start and day_start, each typed as a timezone-naive timestamp.

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
  DATE_TRUNC('year', '2024-08-22 09:15:00'::TIMESTAMP) AS year_start,
  DATE_TRUNC('day', '2024-08-22 09:15:00'::TIMESTAMP) AS day_start

The shape

DATE_TRUNC accepts different field names to round the same datetime to different period boundaries, and the same input can appear in multiple truncations side by side in the same SELECT. The August 22 timestamp truncates to '2024-01-01 00:00:00' for year and to '2024-08-22 00:00:00' for day, giving the pipeline audit both granularities in one row.

Clause by clause

SELECT DATE_TRUNC('year', '2024-08-22 09:15:00'::timestamp) AS year_start returns the start of the calendar year, which is January 1 at midnight. Every datetime inside 2024 truncates to the same value here, which is the property that makes year-level comparisons cheap.
DATE_TRUNC('day', '2024-08-22 09:15:00'::timestamp) AS day_start returns the start of the calendar day, midnight on August 22. The time-of-day component on the input, 09:15:00, is discarded by the truncation, which is the whole point of rounding down to a day boundary.
There is no FROM because both expressions evaluate against the same literal timestamp directly.

The trap

The two expressions look like one calculation reused, but each DATE_TRUNC call evaluates independently against its own copy of the literal. A change to the timestamp has to be made in both places. Repeating the literal is the cost of producing both boundaries on a single literal-driven row; on table-backed data, the column reference would carry the value into both expressions automatically.

You practiced two DATE_TRUNC calls in one statement at different granularities — same input timestamp, different period boundaries side by side.

Return both the year truncation and the day truncation of the timestamp `'2024-08-22 09:15:00'` in a single row

The shape

Clause by clause

The trap

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