Date logic

01The problem

Kingsway Foods' account manager wants a monthly chart of the shipments it booked, January 2025 to August 2026. The obvious query returns 12 rows, not 20:

SELECT strftime('%Y-%m', booking_date) AS month, COUNT(*) AS shipments
FROM shipments
WHERE customer_id = 40
GROUP BY month
ORDER BY month;

Months with no bookings simply don't appear. Charted as it is, the line jumps straight from November 2025 to March 2026, and three quiet months, the most important fact about this customer, disappear.

Nearly every business question has a date in it: this month, last quarter, days to pay, how long since the last order. Date logic is where queries most often go quietly wrong.

02The concept

Dates in SQLite are text

Harbourline stores dates as YYYY-MM-DD text. That format sorts correctly and compares correctly ('2026-03-01' < '2026-04-15'), and SQLite's date functions read it:

The same ideas in other databases

Your job may use a different database. The ideas are identical; only the spelling changes:

"As of" dates

Reports are run as of a date. Harbourline's data ends on 31 August 2026, so "the last 90 days" means after date('2026-08-31', '-90 days'). Avoid date('now') in analysis you'll hand over: the answer changes every day, and nobody can reproduce it.

A calendar for missing periods

GROUP BY can only produce groups that exist in the data. To show every month, build a list of months first and LEFT JOIN the data onto it. A recursive CTE generates the list: it starts with one row, then keeps adding a row based on the previous one until a condition stops it.

03Example

The full 20 months for Kingsway Foods, with zeros where nothing was booked:

WITH RECURSIVE months(month_start) AS (
  SELECT '2025-01-01'                     -- the first row
  UNION ALL
  SELECT date(month_start, '+1 month')    -- each next row
  FROM months
  WHERE month_start < '2026-08-01'        -- stop after August 2026
),
bookings AS (
  SELECT date(booking_date, 'start of month') AS month_start, COUNT(*) AS shipments
  FROM shipments
  WHERE customer_id = 40
  GROUP BY 1
)
SELECT
  strftime('%Y-%m', m.month_start) AS month,
  COALESCE(b.shipments, 0) AS shipments
FROM months AS m
LEFT JOIN bookings AS b ON b.month_start = m.month_start
ORDER BY m.month_start;

Now the gaps show: nothing in December 2025, January or February 2026, and nothing since June 2026. That's a customer to call.

04Walkthrough

1. Run the months CTE on its own (WITH RECURSIVE months(...) AS (...) SELECT * FROM months;) and check it lists 20 months.
2. Note how both sides of the join use the first of the month. Joining on the same key format is what makes the LEFT JOIN line up.
3. Remove COALESCE and see the gaps become NULL. A chart would draw NULL as a break, or not at all; a zero is what the manager means.
4. Run SELECT date('2026-02-14', '-6 days', 'weekday 1'); to find the Monday of that week (9 February).

05Practice

06Challenge

07More practice

08Check your understanding