Expressions, CASE, and dates that behave

CASE: one expression, many outcomes

CASE WHEN total > 100 THEN 'large' ELSE 'small' END is an expression, not a statement — it produces a value, so it can sit in a SELECT, inside an aggregate, or in the ORDER BY. Conditions are tested in order and the first true branch wins, with ELSE optional and NULL as the answer when nothing matches.

Its most useful shape in reporting is banding: turning a continuous number into named buckets — a price band, a size class, a response-time grade — so a GROUP BY can count them. The CASE is the bucket, and the GROUP BY does the counting.

Text and numbers, joined up

|| concatenates: first_name || ' ' || last_name. upper, lower, initcap (which capitalises each word) change case, length counts, split_part(email, '@', 2) takes the piece after the at-sign, and coalesce fills gaps. to_char formats numbers and dates for display — to_char(total, 'FM9,999.99') for money, to_char(placed_on, 'Mon YYYY') for a month label.

Rounding is round(value, places), and casting is :: — total::integer, '2026-09-01'::date. None of this formats anything for you: a database returns values, and presentation is a decision made at the edge, where you can change your mind without touching the data.

Dates, and the empty month

now() is the instant of the statement, CURRENT_DATE the day, and date_trunc('month', placed_on) floors a date to the first of its month — which is what makes "revenue per month" a GROUP BY on one expression. extract(year FROM placed_on) pulls a field out, and date arithmetic speaks intervals: placed_on + interval '30 days', now() - interval '1 hour'.

Comparing ranges is safest with two bounds, the lower inclusive and the upper exclusive: placed_on >= '2026-09-01' AND placed_on < '2026-10-01' cannot double-count a midnight, while a BETWEEN on timestamps can.

The reporting trick worth taking from this chapter: generate_series builds a row for every month in a range, and a LEFT JOIN puts the real data next to it, so a month with no sales shows as a zero instead of vanishing. A report with a hole in it is a report someone will misread.

What a date value actually is

A date is a whole number of days from an internal epoch, and a timestamp is a whole number of microseconds from that epoch. timestamptz keeps that number in UTC and converts it to the session's time zone only when it is displayed or formatted. The stored instant does not change when a reader travels.

date_trunc('month', ts) therefore depends on the time zone in force, because the month boundary is a local idea. On a server set to UTC the answer is stable, but a report that must be right in a named zone should say date_trunc('month', ts AT TIME ZONE 'Europe/Berlin'). Precision about zones is part of the query, not an afterthought.

A month is a range, not a label

The safe way to report a period is to compare the timestamp against a half-open range: at or after the start, strictly before the next start. That works whatever the column's precision, cannot double-count a midnight, and uses an index on the column if one exists. A label such as to_char(ts, 'YYYY-MM') is for the output, after the rows have been chosen.

generate_series gives the ranges a name. It is a set-returning function in the FROM clause, so it produces one row per period; a left join to the data then keeps the empty periods. Building the calendar first, and joining to it, is the difference between a report that shows a zero and one that looks like the month never happened.

Try these next

• Compare date '2026-01-31' + interval '1 month' with the same date plus interval '30 days'.
• Build a weekly calendar and left-join a table that has no rows in one of the weeks.
• Format a date as 'YYYY-MM' and sort the text, then sort the original dates and compare.

Bands, labels, and a calendar

-- A band per row, counted per group.
SELECT CASE WHEN total > 100 THEN 'large' ELSE 'small' END AS size,
       count(*) AS orders,
       sum(total) AS revenue
FROM orders
GROUP BY size
ORDER BY revenue DESC;

-- Labels and formatting are made here, not stored.
SELECT to_char(placed_on, 'Mon YYYY') AS month,
       upper(split_part(email, '@', 2)) AS domain
FROM orders o JOIN customers c ON c.id = o.customer_id;

-- One row per month, whether or not anything sold.
SELECT to_char(m.month_start, 'Mon YYYY') AS month,
       coalesce(sum(o.total), 0) AS revenue
FROM generate_series(
        date '2026-06-01',
        date '2026-09-01',
        interval '1 month'
     ) AS m(month_start)
LEFT JOIN orders o
       ON o.placed_on >= m.month_start
      AND o.placed_on < m.month_start + interval '1 month'
GROUP BY m.month_start
ORDER BY m.month_start;