Dates and months

Almost every business question has a date in it. Not "what did we sell", but "what did we sell last quarter", or "is this month better than the one before". The sales file has one row per day for a hundred days, and nobody wants to read a hundred numbers. They want six, one per month, and this lesson is how you get from one to the other.

Before any of that: the date column here is a real DATE, not text. DuckDB works that out while reading the CSV because the values are in ISO format, year first. That detection is the reason everything below works, and when a date column arrives as text instead, every date function refuses until you cast it. ISO 8601 in your files is the single cheapest thing you can do to make your future self's queries work.

date_trunc rounds a date down

date_trunc('month', date) takes any date and returns the first of its month. The seventeenth of March becomes the first of March, and so does the second, and so does the thirty-first. Group on that and every day in a month lands in the same bucket.

The first argument names the unit, and the useful ones are 'day', 'week', 'month', 'quarter' and 'year'. Swapping one word turns a monthly report into a quarterly one, which is a good demonstration of why this is worth learning properly rather than doing in a spreadsheet afterwards.

The crucial property is that the result is still a date. It sorts chronologically, it can be compared to other dates, and a chart library will treat it as a time axis. That is why date_trunc is better than formatting the date as text, which brings us to the trap.

The string-month trap

strftime(date, '%Y-%m') gives you '2025-03', which is a perfectly good label. Because the year comes first and the month is zero-padded, it even sorts correctly as text. So it works, and I use it for labels.

Change the format and it stops working. '%b %Y' gives you 'Mar 2025', and sorting that alphabetically puts April first and September last. Every analyst has shipped that chart once. The rule that avoids it forever: group and sort by the truncated date, and only format at the very end, in the presentation layer or as a second column that nobody sorts on.

EXTRACT pulls out a part

EXTRACT(month FROM date) returns the number 3 for any March date, in any year. That is a different question from date_trunc, and mixing them up produces confidently wrong reports.

Use EXTRACT for seasonality: which month of the year is busiest across several years, which day of the week gets the most tickets. Use date_trunc for a trend over time. If your file spans two years, grouping by EXTRACT(month FROM date) will silently add last March to this March, and the resulting chart is a seasonality analysis wearing the costume of a trend line.

Other parts worth knowing: year, day, dow for day of week, doy for day of year, and quarter. DuckDB also exposes shorthand functions such as year(date) and monthname(date) if you prefer them to the EXTRACT spelling.

Comparing to a date

Write the literal as DATE '2025-03-01'. Most engines also accept a bare quoted string and cast it for you, but being explicit costs four characters and removes any question about how the string is being read.

For a whole month, BETWEEN DATE '2025-03-01' AND DATE '2025-03-31' is correct on a DATE column, because both ends are included and there is no time component to lose. On a TIMESTAMP column it quietly drops almost the entire last day, and the fix is the half-open range: >= DATE '2025-03-01' AND < DATE '2025-04-01'. Get in the habit of writing it that way even on date columns and you never have to remember which sort of column you are looking at.

Date arithmetic works the way you would hope. date + INTERVAL 7 DAY moves a week forward, and subtracting two dates gives you the number of days between them. That is how you compute an age, a lead time, or a days-to-resolution column on the ticket file.

Towards month over month

A monthly total is one query. Comparing each month to the one before it needs one more idea: reaching into the previous row. That is LAG, and it arrives in lesson 13. Get the monthly table right here, because the growth calculation is built directly on top of it, and a percentage computed from the wrong buckets is worse than no percentage at all.

What to look up next

Look up "date spine" or generate_series for dates. Grouping by month only returns months that have rows, so a month with no sales silently vanishes from your trend line. Joining against a generated list of every month is how you get the zero back.