Dates, strftime & monthly reports

Group by time

Lesson 6 of 20 · 10 min · Module 2: Aggregation that answers real questions

Almost every dashboard groups by time: revenue per month, sign-ups per week. SQLite stores dates as ISO text such as 2026-10-06, and its date functions turn them into the buckets you need.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Bucket dates with strftime
Calculate with date and julianday
Combine time grouping with joins

The idea

strftime('%Y-%m', ordered_at) gives the month, such as 2026-10. Other formats: %W for the week number and %w for the weekday (0 = Sunday). date(ordered_at, '+7 days') moves a date, and julianday() turns dates into numbers you can subtract.

SELECT ordered_at, strftime('%Y-%m', ordered_at) AS month,
       date(ordered_at, '+30 days') AS due
FROM orders LIMIT 3;

01ISO dates sort correctly

Text such as 2026-09-30 sorts in calendar order because the year comes first. That is why ORDER BY month works without converting anything.

02Date arithmetic

julianday('2026-10-06') - julianday('2026-09-30') is 6.0, the number of days between them. date(x, '+1 month', 'start of month') finds the first day of the next month.

SELECT julianday('2026-10-06') - julianday('2026-09-30');

Output

6.0

03Remember the fan-out

Joining items for revenue repeats each order, so the order count needs DISTINCT, exactly as in the previous lesson.

Your turn

For orders that are paid or shipped, return month (YYYY-MM), the number of orders as orders, and revenue_cents. Sort by month.

What the checks look for

Reports August, September and October

Try it in the editor →

Take this with you

strftime turns dates into the buckets a report needs.

Go a little further

Add the average order value per month in cents, rounded to a whole number.