Lesson 10 of 13 · 20 min
Monthly summaries
By the end of this lesson you can produce a monthly report (orders, revenue, rates) for a chosen period, with correct month boundaries, readable labels and chronological sorting.
To summarise by month, every timestamp in a month must map to the same group key. PostgreSQL's date_trunc('month', ts) does exactly that: it cuts a timestamp back to the first instant of its month. 2025-03-17 14:05 and 2025-03-30 23:59 both become 2025-03-01 00:00:00, so they fall into one group.
The truncated value is still a real date/time, which matters: it sorts chronologically, it can be compared with other dates, and charts recognise it as time. Cast it with ::date to drop the 00:00:00 part.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons