Menu
CoddyTech

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:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"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 icon

Challenge

Medium

Return 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in SQL Foundations

Practice on your own: PostgreSQL playground