Of all the questions GreenStore's management asks, most of them have a date inside: how much did we bill this month? which customers haven't bought for six months? how many days did that return take? are we growing quarter on quarter? Up to now all you could do was compare dates with >= and <. This lesson turns the order_date column into an analysis tool.

It also settles 04-05's debt, where EXTRACT appeared in passing to group by year and was left explained "for module 6". And it brings the missing piece for reports: DATE_TRUNC, the function that turns 20 separate orders into a monthly series.

A note on reproducibility: the examples that need "today" use the fixed date DATE '2026-03-01' instead of CURRENT_DATE, so the results you see here match yours. In production you'd use CURRENT_DATE.

1. The temporal types, and why GreenStore uses DATE
2. "Now": CURRENT_DATE, NOW() and CLOCK_TIMESTAMP()
3. Date arithmetic, INTERVAL and AGE()
4. EXTRACT and DATE_PART
5. DATE_TRUNC: the temporal report pattern
6. Formatting with TO_CHAR, parsing with TO_DATE
7. Reports with no gaps using generate_series
8. Comparison table by engine
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

GreenStore uses DATE in every one of its temporal columns —order_date, signup_date, added_date, hire_date, reviews.date, returns.date— because in this model they're calendar facts: an order is "from 4 March", not "from 18:47:03 on 4 March". Storing a time nobody is going to use adds ambiguity without adding information.

In a real shop you'd want the time: to measure preparation time, to know when people buy most, to audit who changed what and when. And as soon as you add the time, the time zone shows up:

The professional recommendation: in a real application, TIMESTAMPTZ is almost always the right choice. TIMESTAMP with no zone looks simpler and it's a trap: it stores a number without saying which clock it belongs to, and the day you have users in two countries there's no way of finding out. TIMESTAMPTZ stores an instant in UTC internally and converts it to each session's zone on reading. And DATE is correct only when the value really is a calendar date: a date of birth, a billing day, a due date.

That's exactly the criterion by which GreenStore uses DATE, and the reason your next application probably shouldn't.

The difference between NOW() and CLOCK_TIMESTAMP() is surprising the first time. If you open BEGIN;, run SELECT NOW(), CLOCK_TIMESTAMP();, wait a few seconds and repeat the query before the COMMIT, the first column returns exactly the same value both times and the second doesn't. NOW() is frozen for the whole transaction, and that's a deliberate guarantee: if a process inserts a hundred rows with NOW(), all hundred share the same stamp and form an identifiable batch. To measure how long something takes inside a transaction, NOW() is no use: you need CLOCK_TIMESTAMP().

Practical consequence: signup_date DATE NOT NULL DEFAULT CURRENT_DATE (the customers DEFAULT from 01-06) is stable and correct. A DEFAULT clock_timestamp() would be volatile and, as you saw in 05-06, it would force a rewrite of the whole table when adding the column.

Two traps in that table. date - date gives an integer and timestamp - timestamp gives an interval: the same - sign with two semantics, so migrating a column from DATE to TIMESTAMP silently changes the type of all your subtractions. And date + INTERVAL returns a TIMESTAMP, not a DATE; if you need a date, add ::DATE.

Look at the first one: 31 January + 1 month = 28 February, because 31 February doesn't exist and PostgreSQL clamps to the last day of the month. A month isn't a fixed number of days: INTERVAL '1 month' and INTERVAL '30 days' are different things.

AGE doesn't return days: it returns years, months and days, the way a person would say it.

Two details: PostgreSQL omits the components that are zero (Javier shows no "0 mons"; Diego, neither months nor days), and AGE is what you want for showing to a human while the subtraction in days is what you want for sorting and comparing.

GreenStore's three returns: the one from the cancelled order was processed in 2 days and the two product-issue ones in 8. With TIMESTAMP instead of DATE this subtraction would return an INTERVAL like 8 days 03:12:00, more precise and less convenient to aggregate.

EXTRACT(field FROM date) pulls out a component. DATE_PART('field', date) does the same with function syntax; EXTRACT is the SQL standard and is the preferable form.

Three warnings: DOW starts on Sunday with 0 (the C convention, not the European one); WEEK is the ISO week, so the first days of January can belong to week 52 or 53 of the previous year; and EXTRACT returns NUMERIC from PostgreSQL 14 onwards. And the performance one, already familiar: WHERE EXTRACT(YEAR FROM order_date) = 2025 works but it wraps the column in a function and can't use the index; the indexable form is 02-03's, WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. You'll measure it in 08-03.

DATE_TRUNC(unit, date) lowers the date to the start of the unit you ask for: 22 October truncated to month is 1 October. That's the whole idea, and it's the basis of almost every temporal report.

Careful with the type: even if you pass it a DATE, DATE_TRUNC returns a TIMESTAMP and that's why you see the 00:00:00. For the report to show a clean date, add ::DATE.

12 months, 20 orders, 47 lines and €727.95 adding up the last column: module 4's canonical figure, now broken down. And the years add up: the first ten months total €603.52 (2025) and the last two, €124.43 (2026). The best month is October 2025 with €97.20; the worst, November 2025 with €31.70.

DATE_TRUNC against EXTRACT for grouping: both work but they aren't equivalent. EXTRACT(MONTH FROM d) returns 3 for March of any year, so March 2025 and March 2026 would fall into the same group; DATE_TRUNC('month', d) keeps the year and sorts chronologically with no tricks. EXTRACT for seasonality, DATE_TRUNC for time series.

TO_CHAR(date, pattern) turns a date into text in whatever format you like.

For the TM prefix to follow the language you want, you set the session's locale:

With lc_time set to en_US.UTF-8 the TM output is English and matches the plain 'Month' pattern; set it to 'fr_FR.UTF-8' and the same query would return mars, octobre and février. That's the whole point of the prefix: the pattern stays the same and the language travels with the session.

Golden rule: format only when presenting. TO_CHAR(d, 'DD/MM/YYYY') produces text, and text sorts alphabetically: '01/12/2025' would come before '04/03/2025'. If you need to sort or group, do it by the date and format afterwards. The only safe exception is 'YYYY-MM', which does sort correctly as text because it's ISO.

TO_DATE(text, pattern) goes the other way, and that's where the classic ambiguity lives:

The same text, two different dates, neither of them incorrect. That's why the pattern is compulsory and why the ISO format YYYY-MM-DD is the only one that's never misread. When you import a CSV, demand the format in the specification and don't deduce it from the data: '03/04/2025' won't tell you which it is.

Here 03-06's and 06-02's promise comes due. A GROUP BY only returns the groups that exist in the data: if there were no signups in a given month, that month doesn't appear and the report lies by omission. Customer signups by month, straight out of the GROUP BY, give 8 rows for 13 months of history. The solution is to manufacture the complete series of months and join it from the left:

13 rows and 15 customers. Now you can see what the GROUP BY was hiding: GreenStore acquired customers steadily until June 2025 and then dried up during the second half —five months with zero signups—. That's a business conclusion that simply didn't exist in the 8-row report.

Three details make the pattern work: the LEFT JOIN goes from the series to the data and never the other way round; you count COUNT(c.id) and not COUNT(*), because COUNT(*) would count the row manufactured by the LEFT JOIN and would give 1 where it should give 0 (04-04); and generate_series with INTERVAL '1 month' generates timestamps, which the ::DATE cleans up and the DATE_TRUNC in the ON makes comparable. The same scheme works for days of the week, quarters or the time axis of a dashboard: it's the most reusable report pattern in the course.

Two warnings are worth the whole table. SQLite has no date type: it stores text, Julian numbers or epoch seconds, and all its functions are STRFTIME over text; it works, but nothing stops somebody putting '04/03/2025' in the same column. And MySQL's and SQL Server's DATEDIFF take their arguments in a different order —DATEDIFF(d2, d1) against DATEDIFF(day, d1, d2)—, so a careless migration flips the sign of every result.

And the mistake to avoid on any engine: storing dates as text. A VARCHAR(10) column holding '2025-03-04' accepts '2025-13-45', can't be added to or subtracted from, sorts wrongly the moment somebody writes '4/3/2025' and can't use a range index effectively. If the value is a date, the type is DATE.

• Storing dates as text. The root error. DATE validates, sorts, subtracts and indexes; VARCHAR does none of that.
• Using TIMESTAMP with no zone in an application with users in several countries. TIMESTAMPTZ is the default choice.
• Expecting date + INTERVAL '1 day' to return a DATE. It returns a TIMESTAMP, just like DATE_TRUNC: hence the 00:00:00 in reports. Add ::DATE.
• Believing INTERVAL '1 month' is 30 days. 2025-01-31 + 1 month is 2025-02-28. And don't confuse DOW (Sunday = 0) with ISODOW (Monday = 1).
• Grouping by EXTRACT(MONTH …) over a multi-year series. March 2025 and March 2026 fall into the same group. For time series, DATE_TRUNC.
• Filtering with EXTRACT(YEAR FROM d) = 2025. Correct but not indexable. Use >= '2025-01-01' AND < '2026-01-01' (02-03).
• Sorting by a date already formatted with TO_CHAR. It sorts as text. Sort by the date and format afterwards; 'YYYY-MM' is the only safe exception.
• Interpreting '03/04/2025' without specifying the pattern. They're two different dates depending on the country.
• Counting with COUNT(*) in a generate_series report. It counts the row manufactured by the LEFT JOIN; count a column of the data table.
• Tip: prefer >= start AND < end to BETWEEN with dates. With DATE they behave the same, but the day the column becomes a TIMESTAMP, BETWEEN will lose the whole last day except for 00:00.
• Tip: store in UTC and convert when presenting, and to debug, fix a reference date (DATE '2026-03-01') instead of CURRENT_DATE: your results will be reproducible tomorrow.

Management wants the complete 2025 monthly report. For each of the twelve months —even if there's nothing— show the month in YYYY-MM format, the number of orders and the product revenue. Use generate_series and explain why January and February appear as zero.

Marketing wants to measure conversion speed: how many days pass between a customer registering and their first order. For customers 1 to 8, show the full name, the signup_date, the date of the first order and the days elapsed, sorted by days. (Hint: MIN(order_date) grouping by customer.)

A colleague has written this seasonality report and claims that "March is our weakest month":

1. What is it really measuring and why is the conclusion fragile with GreenStore's data?
2. Write the DATE_TRUNC version that answers "how is the business evolving month by month?".
3. In what case would the original query be correct?

Solution 1

12 rows, 16 orders and €603.52: module 4's 2025 revenue. January and February come out at zero because GreenStore's first order is from 4 March 2025: the shop existed but wasn't selling yet. Without generate_series those two months wouldn't appear and a line chart would start in March, implying there's no earlier history. The COALESCE (06-04) turns SUM's NULL over an empty set into 0.00.

Solution 2

The business reading is uncomfortable: conversion gets worse as the year goes on, from 49 to 102 days. And there's a bias to declare: more recent customers have had less time to buy. With a plain JOIN you also lose customers 13, 14 and 15, who have never ordered; with a LEFT JOIN they'd appear with NULL in the last two columns, which is the honest answer.

Solution 3

1. EXTRACT(MONTH …) returns 3 for March 2025 as well as for March 2026, so it measures seasonality, not evolution: it groups every March in history together. With GreenStore's data the conclusion is fragile because there's only one March with data and twelve months of history, so each "month of the year" has one or two orders. With 20 orders across 12 months there's no seasonality to measure: there's noise.

2. The time series is section 5's query, and its reading is the opposite: good and bad months alternating, a peak in October 2025 (€97.20) and a trough in November (€31.70), with no clear trend.

3. It would be correct if the question really were seasonal —"in which month of the year do we sell most, averaged over several years?"— and there were enough years to average. What's wrong isn't the function: it's using it to answer a question about evolution.

You now know how to work with time:

• You know the five temporal types and why GreenStore uses DATE, with the firm recommendation that in a real application TIMESTAMPTZ is almost always the right choice. And you can tell NOW() —frozen for the whole transaction— from CLOCK_TIMESTAMP().
• You do date arithmetic knowing that date - date gives whole days, that date + INTERVAL gives a TIMESTAMP and that INTERVAL '1 month' isn't 30 days. And you use AGE() when the reader is a person.
• You pull out components with EXTRACT (DOW starts on Sunday, WEEK is ISO) and truncate with DATE_TRUNC, which turns 20 orders into a monthly series — 04-05's debt, settled. And you know when to use each: EXTRACT for seasonality, DATE_TRUNC for evolution.
• You format with TO_CHAR (including TMMonth with lc_time) and parse with TO_DATE, remembering that '03/04/2025' is two different dates and that sorting text isn't sorting dates.
• You build reports with no gaps using generate_series + LEFT JOIN + COUNT(column), and you've seen what they were hiding: five consecutive months without a single customer signup.

There's a loose end you've seen three times in this lesson and haven't been able to tie: in the signups-per-month report you needed COUNT(c.id) instead of COUNT(*) for 0 to come out; in the monthly revenue one you needed a COALESCE so an empty month wouldn't show NULL; and in the listing of customers with no orders, the date columns would appear empty without saying why. All three are the same problem —what to do when there's no value— and the next lesson, type conversion and handling NULL, solves it once and for all with CAST, COALESCE and NULLIF. It'll also settle another outstanding account: you've been writing ::DATE, ::NUMERIC and ::TEXT for three lessons without anybody explaining what they are.