DATE_TRUNC() for rounding dates to year, month, week or day

DATE_TRUNC() snaps a precise timestamp back to the start of a chosen time period - year, month, week, or day - so scattered moments collapse into tidy groups.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Bucketing Retail Orders with DATE_TRUNC()

SELECT DATE_TRUNC('month', order_date) AS order_month,
       SUM(revenue) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month

Result:

order_month	total_revenue
2024-03-01 00:00:00	203.49
2024-04-01 00:00:00	265.25
2024-05-01 00:00:00	340.75

Step by step

Run the query without SUM so you can see what the unfiltered values look like.

SELECT DATE_TRUNC('month', order_date) AS order_month,
       revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

SUM totals the numbers in a column. Works on numeric columns only.

SELECT DATE_TRUNC('month', order_date) AS order_month,
       SUM(revenue)
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

AS gives the SUM result a friendly column name so the output reads cleanly.

SELECT DATE_TRUNC('month', order_date) AS order_month,
       SUM(revenue) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that rounds order_date down to the start of the year.

SELECT DATE_TRUNC('year', order_date) FROM orders

2. Using store_sales, write a query that rounds sale_date down to the start of the month.

SELECT DATE_TRUNC('month', sale_date) FROM store_sales

3. Using promotions, write a query that rounds start_date down to the start of the month.

SELECT DATE_TRUNC('month', start_date) FROM promotions

4. Using reviews, write a query that rounds review_date down to the start of the week.

SELECT DATE_TRUNC('week', review_date) FROM reviews

5. Using employees, write a query that rounds hire_date down to the start of the month.

SELECT DATE_TRUNC('month', hire_date) FROM employees

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed