Date Truncation and Extraction in SQL
`DATE_TRUNC` and `EXTRACT` are the two primary functions for working with date/time values at a specific calendar granularity. `DATE_TRUNC` rounds a datetime down to a period boundary and returns a datetime. `EXTRACT` pulls a single numeric component out of a datetime and returns a number.
Before this Date and Time Types in PostgreSQL
Builds toward Period-over-Period Analysis, Grouping by Date Periods
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, EXTRACT returns a number.
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:
Every 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.
EXTRACT does something different. It pulls out one numeric component:
SELECT
EXTRACT(year FROM created_at) AS year,
EXTRACT(month FROM created_at) AS month_number,
EXTRACT(dow FROM created_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.
The one thing that trips people up: using EXTRACT(month ...) when you mean DATE_TRUNC('month', ...).
EXTRACT(month FROM created_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', created_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?
10 Date Truncation and Extraction 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.
These problems are part of the Date Truncation and Extraction lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.