Working With Time: Truncating, Intervals, and the Months That Went Missing

Time is the axis almost every report is drawn against — orders per month, growth per quarter, activity per week. It’s also the data type with the most rules: calendars are irregular, months have different lengths, and time zones make “the same instant” and “the same day” two different questions. Postgres has an unusually good set of date and time tools, and this chapter covers the handful you’ll use in nearly every analytical query. The last one, gap-filling, is the one people miss, and missing it produces reports that are quietly wrong.

date_trunc: bucketing time

date_trunc(unit, timestamp) chops a timestamp down to a boundary — the start of its month, week, day, hour — by zeroing everything finer. It’s the workhorse for putting rows into time buckets. Truncating every order_date to 'month' collapses the daily orders into monthly ones, and grouping by that truncated value gives the classic orders-per-month rollup:

SELECT date_trunc('month', order_date)::date AS month,
       count(*) AS orders
FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2023-05-01'
GROUP BY 1 ORDER BY 1;

   month    | orders
------------+--------
 2023-01-01 |   1841
 2023-02-01 |   1579
 2023-03-01 |   1879
 2023-04-01 |   1765
(4 rows)

Every date in January collapses to 2023-01-01, and grouping on that gives one row per month. The ::date cast just trims the 00:00:00 time that date_trunc leaves on; drop it and you’d see 2023-01-01 00:00:00. The unit can be 'week', 'day', 'quarter', 'year', and more — one function covers every calendar bucket you’ll want, which is why date_trunc shows up in almost every time-series query. (MySQL has no direct equivalent; you compose DATE_FORMAT(order_date, '%Y-%m-01') or similar, which is why porting time-series SQL is often the fiddliest part.)

extract, interval, and age: pulling time apart

Three more tools cover the rest of the everyday work. extract(field FROM ts) (also spelled date_part) pulls a numeric component out — the year, the quarter, the day-of-week. Grouping by two extracted parts gives a year-and-quarter breakdown:

SELECT extract(year FROM order_date)  AS yr,
       extract(quarter FROM order_date) AS qtr,
       count(*) AS orders
FROM orders
GROUP BY 1,2 ORDER BY 1,2;

  yr  | qtr | orders
------+-----+--------
 2023 |   1 |   5299
 2023 |   2 |   5426
 ...
 2025 |   3 |   5406
(11 rows)

Eleven quarters, from 2023 Q1 through 2025 Q3, which pins the data’s span. extract reaches finer components too. The dow field returns day-of-week as 0 (Sunday) through 6 (Saturday), which is how you test for a weekday effect — do orders cluster on any particular day?

SELECT extract(dow FROM order_date) AS dow,
       to_char(order_date, 'Dy')     AS day,
       count(*)                      AS orders
FROM orders
GROUP BY 1, 2
ORDER BY 1;

 dow | day | orders
-----+-----+--------
   0 | Sun |   8538
   1 | Mon |   8500
   2 | Tue |   8591
   3 | Wed |   8618
   4 | Thu |   8726
   5 | Fri |   8506
   6 | Sat |   8521
(7 rows)

Orders are flat across the week here — the seed spreads them evenly — but on real data this is the query that reveals the weekend dip or the Monday spike. to_char alongside gives the readable day name for the report; extract gives the number to sort on. (Watch out: extract(dow ...) counts Sunday as 0, but extract(isodow ...) counts Monday as 1 through Sunday as 7 — pick the one your week starts on.)

interval is the type for a duration. You add it to a date to do calendar math: + interval '30 days' lands you thirty days later, correctly crossing month and year boundaries. age(a, b) gives the gap between two dates as a human-readable interval of years, months, and days rather than a raw day count:

SELECT date '2025-09-27' + interval '30 days'  AS plus_30,
       age(date '2025-09-27', date '2023-01-01') AS span;

       plus_30       |          span
---------------------+------------------------
 2025-10-27 00:00:00 | 2 years 8 mons 26 days
(1 row)

Thirty days past September 27 is October 27, and the bookshop’s date range spans 2 years, 8 months, 26 days. Interval arithmetic is how you express windows like “orders in the last 30 days” (order_date >= current_date - interval '30 days') without hand-counting calendar days.

The months that went missing

Here’s the trap. The orders-per-month query above only returns months that have orders. If a month had none, there’s no row for it to aggregate, so it simply vanishes from the result. Your chart then draws a line straight from the month before to the month after, silently pretending the empty period didn’t happen. For a busy metric you might never notice; for anything sparse it’s a real defect. Watch it happen on a single customer’s orders:

SELECT date_trunc('month', order_date)::date AS month, count(*) AS orders
FROM orders
WHERE customer_id = 7
GROUP BY 1 ORDER BY 1;

   month    | orders
------------+--------
 2023-03-01 |      1
 2023-07-01 |      1
 2023-08-01 |      1
 2023-09-01 |      1
 ...

Customer 7 ordered in March, then not again until July — April, May, and June aren’t zero here, they’re absent. The count skipped straight from March to July. Any downstream code assuming twelve rows per year is now reading the wrong month against the wrong number.

The fix is to generate the complete set of months first and left-join the data onto it. generate_series(start, stop, interval) produces one row per month across the whole range, whether or not any order falls in it. The LEFT JOIN attaches counts where they exist and leaves NULL where they don’t. And count(o.order_id) turns those NULLs into a real 0 (recall from Series 1 that count of a column ignores NULLs):

SELECT m.month::date AS month,
       count(o.order_id) AS orders
FROM generate_series(date '2023-01-01', date '2023-12-01', interval '1 month') AS m(month)
LEFT JOIN orders o
  ON date_trunc('month', o.order_date) = m.month
 AND o.customer_id = 7
GROUP BY m.month
ORDER BY m.month;

   month    | orders
------------+--------
 2023-01-01 |      0
 2023-02-01 |      0
 2023-03-01 |      1
 2023-04-01 |      0
 2023-05-01 |      0
 2023-06-01 |      0
 2023-07-01 |      1
 2023-08-01 |      1
 2023-09-01 |      1
 2023-10-01 |      0
 2023-11-01 |      0
 2023-12-01 |      0
(12 rows)

Twelve rows, one per month, with the empty months now honestly showing 0. Note the customer_id = 7 filter sits in the join’s ON clause, not a WHERE. Put it in WHERE and it would test the NULL rows from unmatched months, discard them, and undo the entire point of the left join. This is the generate-series-left-join pattern, and it belongs in any time report where a period can legitimately be empty.

A word on time zones

The bookshop stores dates, which have no zone, so the queries above are unambiguous. Real event data usually lives in timestamptz — a timestamp that knows it’s an instant in time — and there “what day is this” depends on where you’re standing. date_trunc takes an optional third argument for exactly this: the zone to bucket in.

SELECT date_trunc('day', timestamptz '2025-09-27 23:30:00+00')                  AS utc_bucket,
       date_trunc('day', timestamptz '2025-09-27 23:30:00+00', 'America/New_York') AS ny_bucket;

       utc_bucket       |       ny_bucket
------------------------+------------------------
 2025-09-27 00:00:00+00 | 2025-09-27 04:00:00+00

The same instant — 23:30 UTC on the 27th — is 19:30 on the 27th in New York. So its NY day still starts at 2025-09-27, but that local midnight is 04:00 UTC. Bucket in UTC when your instant is late enough and the two calendars disagree about which day it lands on. Whenever a report crosses zones, decide explicitly which zone defines “a day,” and pass it.

Final thoughts

Time work is four tools and one discipline. date_trunc buckets instants into periods; extract/date_part pull components out; interval arithmetic and age do calendar math correctly across irregular month lengths. The discipline is gap-filling: a GROUP BY over dates only ever returns periods that had rows, so empty periods disappear and a chart lies by omission. Generate the full calendar with generate_series, left-join the data onto it, and count the NULLs to zero — with the entity filter in the ON clause, not WHERE. And when instants carry a zone, remember that “the same day” is a question with a location in it, and date_trunc’s zone argument is how you answer it. Next we take the same reshape-the-raw-value instinct to text.

Next: Text, matched and reshaped