Time-based reporting groups rows by a period such as a day or month. date_trunc maps each timestamp to the start of its period, and correct range filters use a half-open interval rather than BETWEEN, which behaves surprisingly with timestamps.
Before you start
You should be comfortable with GROUP BY and timestamp columns. This article uses PostgreSQL’s date_trunc and timestamptz.
Step-by-step walkthrough
Step 1: Truncate to the period you report
date_trunc('month', created_at) returns the first instant of that month, so grouping by it collapses every row in the month into one bucket. Choose the unit that matches the report, and alias the expression so the GROUP BY and ORDER BY read clearly.
Step 2: Filter with a half-open range
Write created_at >= '2026-01-01' AND created_at < '2026-02-01' instead of BETWEEN '2026-01-01' AND '2026-02-01'. BETWEEN is inclusive on both ends, so it also includes exactly 2026-02-01 00:00:00, which overlaps the next month and double-counts rows on the boundary.
Step 3: Be explicit about the timezone
timestamp has no zone and timestamptz stores an instant. Grouping a timestamptz truncates in the session timezone, so the same data can bucket differently across sessions. Set the timezone or convert explicitly so the report is reproducible.
Worked scenario
The query groups orders by month and orders the buckets chronologically.
SELECT
date_trunc('month', created_at) AS month,
count(*) AS orders
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-07-01'
GROUP BY 1
ORDER BY 1;Walk through the example
The WHERE clause selects the first half of the year with a half-open range, so no January or July boundary row is double-counted. date_trunc('month', ...) maps each remaining row to its month start, and GROUP BY 1 collapses them into monthly buckets. Ordering by the bucketed value is chronological because truncation preserves order.
Common mistake
Using BETWEEN on timestamps and including an extra boundary row, or grouping by a raw timestamp and getting one bucket per microsecond. Another is mixing timestamp and timestamptz in a comparison, which triggers an implicit conversion that depends on the session timezone.
Verify the behavior
Insert rows exactly at a month boundary and confirm the half-open range assigns each to one bucket only. Compare a date_trunc grouping with per-month count(*) queries to confirm the totals match. Run the same query under two session timezones and observe the difference when the column is timestamptz.
Interview exercise
Why can a monthly report show different counts for two analysts running the same query?
Answer and reasoning
If the column is timestamptz and the query truncates in the session timezone, a row near midnight can fall into a different month depending on the session’s zone. The data is identical; the bucketing differs. Fix it by setting the timezone explicitly or truncating after converting to a fixed zone, so both analysts bucket the same way.
Continue learning
Compare time semantics in SQL window functions and aggregation grain. Read the PostgreSQL date/time functions documentation and try the SQL interview questions.