TopolearnNearly every report is 'per day', 'last 30 days' or 'per month', and time zone bugs are subtle.
PostgreSQL's date and time support is rich and mostly standard. The building blocks:
- Current time:
now()(the transaction's start time, astimestamptz),current_date,clock_timestamp()(wall clock, changes during a transaction). - Intervals:
interval '30 days',interval '2 hours'. Add them to dates and timestamps:now() - interval '30 days'. Subtracting two timestamps gives an interval; subtracting twodates gives an integer number of days. - Truncation:
date_trunc('month', ordered_at)rounds down to the start of the month. It is the workhorse of time-bucketed reports. - Extraction:
extract(year FROM ordered_at),extract(dow FROM ...)(day of week, Sunday = 0),extract(epoch FROM ...)(seconds since 1970). - Formatting:
to_char(ordered_at, 'YYYY-MM-DD'); parsing withto_dateandto_timestamp.
Time zones deserve care. A timestamptz stores an absolute instant (internally UTC) and is displayed in the session's TimeZone setting. AT TIME ZONE 'Asia/Yerevan' converts it to that zone's local wall time. "Per day" is therefore ambiguous: whose day? Truncate in the business's zone, date_trunc('day', ordered_at AT TIME ZONE 'Europe/London'), or users near midnight will be counted on the wrong day.
generate_series(start, stop, interval) produces a row per step. Left-joining it to your data fills in the days with no activity, so charts show zeros instead of gaps.
Dialects differ heavily here: SQL Server uses DATEADD, DATEDIFF and GETDATE(); MySQL uses DATE_ADD(d, INTERVAL 30 DAY) and DATE_FORMAT. The concepts carry over, the spelling does not.
Example
SELECT now() - interval '30 days' AS a_month_ago,
date '2026-03-15' - date '2026-01-05' AS days_between, -- 69
extract(dow FROM date '2026-03-15') AS weekday; -- 0 (Sunday)
-- Revenue per month in the shop's time zone
SELECT date_trunc('month', ordered_at AT TIME ZONE 'Europe/London') AS month,
SUM(total) AS revenue
FROM orders WHERE status <> 'cancelled'
GROUP BY 1 ORDER BY 1;
-- 2026-01-01 00:00:00 | 68.50
-- 2026-02-01 00:00:00 | 198.99
-- 2026-03-01 00:00:00 | 238.00
-- Every day of the week, including days without orders
SELECT d::date AS day, COUNT(o.id) AS orders
FROM generate_series(date '2026-02-09', date '2026-02-15', interval '1 day') AS d
LEFT JOIN orders o ON o.ordered_at::date = d::date
GROUP BY d ORDER BY d;
-- 2026-02-09 | 0
-- 2026-02-10 | 1
-- 2026-02-11 | 1
-- 2026-02-12 | 0 ...Remember
- Store instants as timestamptz; convert with AT TIME ZONE for local-day logic
- date_trunc buckets timestamps; extract pulls out parts
- Timestamp arithmetic uses intervals; date minus date gives days
- generate_series + LEFT JOIN fills gaps in time series
Watch out for
- Grouping by UTC day for a business whose 'day' is in another time zone.