Month Buckets
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 9 of 22.
date_trunc moves a timestamp to the start of a chosen calendar unit. Truncating to month preserves the year and month while setting the day to one and the clock to midnight.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, date_trunc('month', ordered_on::timestamp)::date AS month_start FROM orders ORDER BY id;January rows become 2025-01-01 and February rows become 2025-02-01. The date cast gives a compact month label. The week unit similarly moves to Monday at midnight. These timestamps have no time zone; instant-based business reports need an explicit business zone.
Use date_trunc to build calendar buckets that retain year information.
Challenge
MediumReturn id and week_start, the Monday starting each order week. Use date_trunc with week and return a date. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
2Dates and Time
Read Dates ExplicitlyDays and IntervalsExtract Calendar PartsMonth BucketsDispatch CalendarPractice on your own: PostgreSQL playground