◀ Course contents Part 2 · Module 2-04

Working with Dates

Filtering, grouping and measuring time

Nearly every question worth asking of data has a "per month" or "since when" in it. Postgres has a real DATE type, which means the database understands what a month is — so the gap between two dates is subtraction, and the first of the month is a function call rather than string surgery.

Ready?

A Real Date, and How to Write One

ordered_at is a DATE — an actual calendar date, not text that looks like one. Postgres knows it has a year, a month and a day, and that is what makes everything else in this module possible.

It also means the database can refuse nonsense. Insert '2023-02-31' into a DATE column and you get date/time field value out of range; insert it into a text column and you have stored the thirty-first of February forever.

You compare a date against a date literal. Both of these work:

WHERE ordered_at >= DATE '2024-01-01'
WHERE ordered_at >= '2024-01-01'

The second relies on Postgres reading the string as a date because it is being compared to one. The first says so outright. Prefer DATE '…': it is unambiguous, and a typo fails immediately rather than somewhere later where the cause is harder to see.

A type is not paperwork. Everything in the rest of this module is a thing the database can only do because it knows the column is a date — and the impossible value it refuses on the way in is the one you would otherwise find in a report a year later.

MIN and MAX work on dates too, so the oldest account on file is a MIN(signup_date) — no sorting, no LIMIT.

DATE, TIMESTAMP, and the one that bites

DATE is a day with no time. TIMESTAMP adds a time, and TIMESTAMPTZ adds a time zone and is what you almost always want for "when something happened". The columns here are DATE to keep the lessons about dates — but lesson 3 is about what happens to a range filter the day a column like this becomes a timestamp, because that day comes.

Quick check

Why can you write cancelled_on - started_on and get a number of days?

to_char, EXTRACT, date_trunc

Three ways to get a part of a date, and the difference between them is what they hand back.

`to_char(d, 'YYYY-MM')`

Text. Any format you ask for: 'YYYY', 'MM', 'Mon', 'Day'. For labels.

`EXTRACT(YEAR FROM d)`

A number. For arithmetic and comparisons. Also MONTH, DAY, DOW, QUARTER.

📅

`date_trunc('month', d)`

Still a date — the first instant of the period. For when the result feeds a chart, a join or more arithmetic.

For a trend, to_char(d, 'YYYY-MM') gives 2024-03: unique per month across years, and still largest-unit-first, so it sorts chronologically as plain text.

SELECT to_char(ordered_at, 'YYYY-MM') AS mth,
       COUNT(*) AS n
FROM orders
GROUP BY to_char(ordered_at, 'YYYY-MM')
ORDER BY mth;

Never group on the month alone. to_char(d, 'MM') is '03' with no year in it, so every March in the table lands in the same pile. On two years of data that quietly doubles each month; on five it quintuples them, and the chart still looks like a chart.

A month

`to_char(d, 'YYYY-MM')`

2024-03. One row per month per year, sorts chronologically as text.

Every March at once

`to_char(d, 'MM')`

03. Twelve rows however many years you have, each silently summing all of them.

The GROUP BY has to repeat the expression: the alias is made at step 5 and GROUP BY runs at step 3, exactly as the last module set out, so GROUP BY mth is an error. GROUP BY 1 — the output position — is the accepted shorthand.

Quick check

Three years of sales, grouped by to_char(sold_at, 'MM'). What does the "March" row contain?

The Half-Open Range

BETWEEN works on dates and is inclusive at both ends:

WHERE ordered_at BETWEEN '2023-01-01' AND '2023-01-31'

That looks like January and is a bug waiting for one change. The moment these columns hold a time as well as a date — and they always eventually do — '2023-01-31 14:02' sorts after '2023-01-31' and falls outside the range. You lose every row from the afternoon of the last day, in a report that still adds up and still looks right.

The form that never breaks is a half-open range: from the start of the period, up to but not including the start of the next one.

WHERE ordered_at >= '2023-01-01'
  AND ordered_at <  '2023-02-01'

The upper bound of a BETWEEN is a single instant — midnight at the start of the last day. A half-open range covers the whole final day whatever precision the column turns out to have, and needs no knowledge of how long the month is.

It has a second benefit worth as much as the first: you never have to know how long the month is. No 28, 30 or 31, no leap years, no February special case. The end of one period is the start of the next, which you already know.

The month that lost a day

A finance export ran on BETWEEN the first and last of the month for three years without incident, because the source system recorded dates only. When it started recording timestamps, the export lost every transaction after midnight on the final day — about three percent of the month, every month. It was found in an audit, not by the report, because a total that is three percent low is not obviously wrong.

Quick check

You want all of February 2024, which has 29 days. Which filter needs no thought about that?

Durations, Date Maths, and the Month That Is Not There

Subtracting two dates gives an integer number of days. No function, no cast, no conversion:

cancelled_on - started_on   -- 172

This is the clearest thing a real date type buys you. Databases without one need a function for it, and hand back a float you then have to round.

Note what happens when one side is NULL: a subscription still running has NULL in cancelled_on, so the arithmetic gives NULL — every live subscription silently drops out of an average lifetime, exactly as module 1-05 promised.

Adding to a date works two ways, and the difference matters:

started_on + 30                          -- a DATE, 30 days later
started_on + INTERVAL '1 month'          -- a TIMESTAMP
(started_on + INTERVAL '1 month')::date  -- a DATE again

Adding a plain integer to a DATE adds days and keeps it a DATE. An INTERVAL can say months and years, which days cannot express — but it returns a timestamp, so it usually wants ::date on the end.

Intervals are also how you get the last day of a month without knowing how long it is:

(date_trunc('month', d) + INTERVAL '1 month - 1 day')::date

Finally, the thing every trend query has to reckon with. Grouping produces only the periods that have rows. A month with no sales is not a zero — it is absent, and a line chart drawn straight from that result joins across the gap as if the quiet month never happened.

What you get

Ten rows for a twelve-month year

Two months had nothing, so they produced no group. Nothing in the result says they existed.

What a report needs

Twelve rows, two of them zero

Requires a list of all twelve months to join the totals onto — which is a LEFT JOIN, and that is Part 3.

Quick check

AVG(cancelled_on - started_on) over all subscriptions. What is that the average of?

0 of 9 completed

Loading the tables…

To do

ordered_at is a DATE column — an actual date, not text that looks like one. Postgres knows it has a year, a month and a day, which is what makes everything else in this module possible.

You compare it against a date literal. Both of these work:

WHERE ordered_at >= DATE '2024-01-01'
WHERE ordered_at >= '2024-01-01'

The second relies on Postgres reading the string as a date because it is being compared to one. The first says so. Prefer DATE '…': it is unambiguous, and it fails loudly on a typo instead of at some later point where the reason is harder to see.

Your task: return order_id and ordered_at for every order placed on or after 1 January 2024, oldest first.

query.sql

SELECT
FROM orders
WHERE
ORDER BY

PostgreSQL

Hint

WHERE ordered_at >= DATE '2024-01-01' — then ORDER BY ordered_at.

Output

To do

Two ways to get a part of a date, and they return different types.

EXTRACT(YEAR FROM ordered_at) gives a number — good for arithmetic and comparisons.

to_char(ordered_at, 'YYYY') gives text, formatted however you ask: 'YYYY', 'MM', 'YYYY-MM', 'Mon', 'Day'. Good for labels.

Note the pattern is upper case. 'YYYY' is a four-digit year; 'yyyy' happens to work but 'MM' and 'mm' are month while 'MI' is minutes, so the conventional casing is worth keeping.

Your task: from orders, return each year as text in a column named yr and the number of orders in it as n, oldest first.

query.sql

SELECT
FROM orders
GROUP BY
ORDER BY yr;

PostgreSQL

Hint

SELECT to_char(ordered_at, 'YYYY') AS yr, COUNT(*) AS n FROM orders GROUP BY 1 ORDER BY yr; — GROUP BY 1 means "the first output column".

Output

To do

A monthly trend needs a label that is unique per month across years. to_char(d, 'YYYY-MM') gives 2024-03, which is exactly that — and because it still runs largest unit first, sorting it as text sorts the months correctly.

Never group on the month alone. to_char(d, 'MM') is '03' with no year in it, so every March in the table lands in one pile. On two years of data that quietly doubles each month.

Your task: from orders, for orders placed in 2024 only, return the month as mth and the count as n, oldest month first.

query.sql

SELECT
FROM orders
WHERE ordered_at >= DATE '2024-01-01'
GROUP BY
ORDER BY mth;

PostgreSQL

Hint

to_char(ordered_at, 'YYYY-MM') AS mth, then GROUP BY that same expression.

Output

To do

to_char gives you a label. date_trunc gives you a date — the first instant of the period the value falls in.

date_trunc('month', DATE '2024-03-06')   -- 2024-03-01 00:00:00

It returns a timestamp, so ::date trims the midnight off. Grouping on this rather than on text is what you want when the result feeds a chart or another query, because the value is still a date and can still be compared, sorted and subtracted.

Your task: from orders, for 2024 only, return the first day of each month as a date in a column named mth and the count as n, oldest first.

query.sql

SELECT
FROM orders
WHERE ordered_at >= DATE '2024-01-01'
GROUP BY
ORDER BY mth;

PostgreSQL

Hint

date_trunc('month', ordered_at)::date AS mth — and group by the same expression.

Output

To do

BETWEEN from module 1-02 works on dates, and it is inclusive at both ends.

Your task: return order_id and ordered_at for orders placed in the first quarter of 2023 — 1 January to 31 March inclusive — oldest first.

query.sql

SELECT
FROM orders
WHERE
ORDER BY ordered_at;

PostgreSQL

Hint

WHERE ordered_at BETWEEN DATE '2023-01-01' AND DATE '2023-03-31' — both ends included.

Output

To do

That BETWEEN is correct today and is one schema change from being wrong. These columns hold a date; the moment one holds a timestamp, 2023-03-31 14:02 sorts after 2023-03-31 00:00 and falls outside the range. A whole afternoon, silently missing, in a report that still adds up.

The habit that never breaks is a half-open range: from the start of the period, up to but not including the start of the next one.

WHERE ordered_at >= DATE '2023-01-01'
  AND ordered_at <  DATE '2023-04-01'

It also means you never have to know how long the month is — no 28, 30 or 31, and no leap years. The end of one period is the start of the next, which you already know.

Your task: rewrite the Q1 2023 filter as a half-open range, returning order_id and ordered_at, oldest first.

query.sql

-- >= the start, < the start of the next quarter. No BETWEEN.
SELECT
FROM orders
WHERE
ORDER BY ordered_at;

PostgreSQL

Hint

WHERE ordered_at >= DATE '2023-01-01' AND ordered_at < DATE '2023-04-01' — note the strict < on the upper bound.

Output

To do

This is where a real date type earns itself. Subtracting two dates gives an integer number of days. No function, no cast, no conversion:

cancelled_on - started_on   -- 172

Databases without a date type need a function for this, and the answer comes back as a float you then have to round. Here it is arithmetic.

Watch what NULL does, though: a subscription still running has NULL in cancelled_on, so the subtraction is NULL and the row drops out of any average — exactly as module 1-05 warned.

Your task: from subscriptions, for the ones that have been cancelled, return subscription_id and how many days they ran as days, longest first.

query.sql

SELECT
FROM subscriptions
WHERE cancelled_on IS NOT NULL
ORDER BY

PostgreSQL

Hint

(cancelled_on - started_on) AS days — and the WHERE is IS NOT NULL, not <> NULL, as module 1-05 covered.

Output

To do

Adding to a date works two ways in Postgres, and the difference matters.

started_on + 30 — adding a plain integer to a DATE adds days, and the result is still a DATE.

started_on + INTERVAL '30 days' — an interval can say months and years too, which days cannot express. But it returns a timestamp, so it usually wants ::date on the end.

started_on + INTERVAL '1 month'          -- timestamp
(started_on + INTERVAL '1 month')::date  -- date

Intervals are also how you get the last day of a month without knowing its length: (date_trunc('month', d) + INTERVAL '1 month - 1 day')::date.

Your task: from subscriptions, return subscription_id, started_on, and the date 30 days after it as trial_end, ordered by subscription_id.

query.sql

SELECT
FROM subscriptions
ORDER BY subscription_id;

PostgreSQL

Hint

started_on + 30 AS trial_end — adding an integer to a date adds days and keeps it a date.

Output

To do

Put the pieces together: format the date into a month label, group on it, count, sort. This is the shape of nearly every trend query anyone writes.

Watch what is not in the result. December has no signups, so there is no December row — not a zero, an absence. Filling those gaps needs a list of months to join against, which is Part 3.

Your task: from users, return the signup month as mth and the number of signups as n, oldest month first.

query.sql

SELECT
FROM users
GROUP BY
ORDER BY mth;

PostgreSQL

Hint

to_char(signup_date, 'YYYY-MM') AS mth, COUNT(*) AS n, grouped by the same expression.

Output

The monthly trend

To do

The board wants the 2023 sales trend: one line per month, so they can see the shape of the year. Only paid orders count — the pending and refunded ones are not sales.

Your task: from orders, over paid orders placed in 2023, return exactly these three columns in this order:

mth — the month as text, YYYY-MM
n — how many orders that month
revenue — the total amount that month

Oldest month first. Use a half-open range for the year rather than BETWEEN — on or after 1 January 2023, and before 1 January 2024.

One month of 2023 had no paid orders at all. It will simply not appear — that is the correct behaviour here rather than something to work around, and noticing which month is missing is part of reading the result.

query.sql

-- Paid orders in 2023, one row per month, oldest first.
SELECT
FROM orders
WHERE
GROUP BY
ORDER BY

PostgreSQL

Hint

to_char(ordered_at, 'YYYY-MM') AS mth, then WHERE status = 'paid' AND ordered_at >= DATE '2023-01-01' AND ordered_at < DATE '2024-01-01', then GROUP BY the same expression, then ORDER BY mth.

Output

Sign in to open this module

Working with Dates

A Real Date, and How to Write One

DATE, TIMESTAMP, and the one that bites

Quick check

to_char, EXTRACT, date_trunc

to_char(d, 'YYYY-MM')

EXTRACT(YEAR FROM d)

date_trunc('month', d)

to_char(d, 'YYYY-MM')

to_char(d, 'MM')

Quick check

The Half-Open Range

The month that lost a day

Quick check

Durations, Date Maths, and the Month That Is Not There

Ten rows for a twelve-month year

Twelve rows, two of them zero

Quick check

A real date, and how to write one

Pull the year out

Group by month

date_trunc keeps it a date

A window of dates

The range that survives a timestamp

Subtract one date from another

A date a month from now

A monthly count with the gaps showing

The monthly trend

Notification

`to_char(d, 'YYYY-MM')`

`EXTRACT(YEAR FROM d)`

`date_trunc('month', d)`

`to_char(d, 'YYYY-MM')`

`to_char(d, 'MM')`