Date Truncation and Extraction in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Date and Time Types in PostgreSQL
Builds toward Period-over-Period Analysis, Grouping by Date Periods
What are DATE_TRUNC and EXTRACT in SQL?
DATE_TRUNC collapses timestamps into calendar period groups. EXTRACT pulls a single numeric component out of a timestamp.
Both work on date/time values, but they answer different questions. DATE_TRUNC is a grouping tool: it rounds a timestamp down to the nearest period boundary — month, week, year, day, hour. Every timestamp within March 2024 truncated to month becomes 2024-03-01 00:00:00. Group by that value and you get one row per month. EXTRACT is a decomposition tool: it pulls out one component of a timestamp as a number — the year, the month number (1–12), the day of the week, the hour. The outputs are different types: DATE_TRUNC returns a timestamp type — a TIMESTAMPTZ column comes back as TIMESTAMPTZ, not as a date — and EXTRACT returns a number.
How do you group rows by month in PostgreSQL?
You're building a monthly revenue report. You want one row per calendar month with the revenue total for that month. DATE_TRUNC is the right tool:
SELECT
DATE_TRUNC('month', ordered_at) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY monthEvery order from January 2024 truncates to 2024-01-01 00:00:00. Every order from February truncates to 2024-02-01 00:00:00. The GROUP BY collapses them into monthly buckets. You get one row per month.
What does EXTRACT return in PostgreSQL?
EXTRACT does something different. It pulls out one numeric component:
SELECT
EXTRACT(year FROM ordered_at) AS year,
EXTRACT(month FROM ordered_at) AS month_number,
EXTRACT(dow FROM ordered_at) AS day_of_week
FROM ordersyear gives you the four-digit year. month_number gives you 1 through 12. dow gives you the day of week, where 0 is Sunday and 6 is Saturday. These are numbers — useful for filtering (orders placed on Tuesdays), comparisons, or further arithmetic. Two other fields worth knowing: quarter returns 1 through 4, and epoch returns the number of seconds since January 1, 1970 as a decimal — useful for computing precise durations or interfacing with systems that store time as a Unix timestamp.
What is the difference between EXTRACT and DATE_TRUNC?
The one thing that trips people up: using EXTRACT(month ...) when you mean DATE_TRUNC('month', ...).
EXTRACT(month FROM ordered_at) returns a number between 1 and 12 with no year attached. An order from March 2023 and an order from March 2024 both return 3. Group by that value and you collapse all Marches from all years into one row. That's occasionally the intent. Usually, it's a bug.
DATE_TRUNC('month', ordered_at) includes the year in the boundary value. 2024-03-01 and 2023-03-01 are different values, so they stay in different groups. For time-series reports, DATE_TRUNC is almost always the right tool.
You want to group sales by calendar month and show one row per month per year. Which function do you use?
Practice DATE_TRUNC and EXTRACT in SQL
Brightlane's reporting pipeline assigns each event to its calendar month for period-based grouping.
Write a query to return the month boundary produced by truncating the timestamp '2024-03-15 14:32:07' to month precision.
Output:
- A single row with one column,
month_start, typed as a timezone-naive timestamp.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 DATE_TRUNC and EXTRACT practice problems
Write a query to return the month boundary produced by truncating the timestamp '2024-03-15 14:32:07' to month precision.
Write a query to return the year component of the date '2024-03-15'.
Write a query to return the month number for the timestamp '2024-08-22 09:15:00'.
Write a query to return the week boundary produced by truncating the timestamp '2024-03-15 14:32:07' to week precision.
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.
Write a query to return the day-of-week number for that timestamp.
Write a query to return the quarter number and the hour-of-day for the timestamp '2024-08-22 14:30:00' in a single row.
Write a query to return the Unix epoch seconds for the timestamp '2024-01-01 00:00:00'.
Write a query to return the month component of '2024-03-15' and the month component of '2023-03-15' in a single row.
Write a query to return both the day-of-week number for that date and the start of its calendar week in a single row.
Start learning to practice all 10 DATE_TRUNC and EXTRACT problems, with instant grading and mastery tracking.
Deeper guides on DATE_TRUNC and EXTRACT
- adding an interval after truncating a date
Add an integer or an INTERVAL, and see which one leaves you holding a timestamp.
- formatting a truncated date for display
to_char() patterns with real output, the MM-for-minutes bug, and month labels that sort April first.
Common questions about DATE_TRUNC and EXTRACT
Does DATE_TRUNC round or truncate?
It truncates. DATE_TRUNC always moves a value down to the start of its period and never up, so 23:59 on January 31st truncates to January 1st, not February 1st. Nothing below the period survives: the day, hours and minutes are all reset.
Does DATE_TRUNC return a date or a timestamp?
Always a timestamp, even when you give it a date. Pass a TIMESTAMP and you get a TIMESTAMP back; pass a DATE and PostgreSQL promotes it, so you get a TIMESTAMPTZ. When you want a plain calendar day for display or for joining, cast the result with ::date.
What does EXTRACT return?
A single number, not a point in time. EXTRACT(MONTH FROM order_date) returns 1 for January, and the result is a numeric value that knows nothing about the year or the day it came from. Reach for EXTRACT when you need one field as a number, and DATE_TRUNC when you need a period you can still sort as a date.
Can DATE_TRUNC truncate to a quarter?
Yes. DATE_TRUNC('quarter', value) returns the first moment of the calendar quarter: January 1st, April 1st, July 1st or October 1st. A date in late May truncates to April 1st.
