Dates, intervals and generate_series

Four types, and which to use

• date — a calendar day, no time.
• timestamp (without time zone) — a wall-clock reading with no zone; it does not identify an instant on its own.
• timestamptz (with time zone) — an instant. PostgreSQL stores it in UTC and displays it in the session's TimeZone setting; it does not store the zone you typed. This is the right type for "when did it happen". (This sandbox's session zone is UTC.)
• interval — a duration in months, days and microseconds, kept separately because a month is not a fixed number of days.

generate_series and its result type

generate_series(start, stop, step) returns one row per step. With integers it returns integers. With dates and an interval step, there is no date version of the function, so the dates are promoted and you get timestamptz back. Cast to date when you mean days.

1SELECT pg_typeof(g)   AS raw_type,2       g::text        AS raw_value,3       g::date::text  AS as_date4FROM generate_series(date '2024-01-30', date '2024-02-02', interval '1 day') AS g;

Filling gaps in a report

Grouping by day only produces days that have rows. A chart built from that silently skips quiet days. Generate the calendar first, then LEFT JOIN the facts onto it and COALESCE the empty sums to 0.

1CREATE TABLE orders (id int PRIMARY KEY, placed_at timestamptz NOT NULL, amount numeric(10,2) NOT NULL);2INSERT INTO orders VALUES3  (1, '2024-03-01 09:15+00', 20.00),4  (2, '2024-03-01 17:40+00', 5.50),5  (3, '2024-03-03 11:00+00', 12.00),6  (4, '2024-03-05 23:59+00', 8.00);

1SELECT d::date::text AS day,2       count(o.id) AS orders,3       COALESCE(sum(o.amount), 0) AS revenue4FROM generate_series(date '2024-03-01', date '2024-03-05', interval '1 day') AS d5LEFT JOIN orders o6  ON o.placed_at >= d AND o.placed_at < d + interval '1 day'7GROUP BY d8ORDER BY d;

Arithmetic and ranges

• date - date is an integer number of days; date + integer is a date.
• date + interval is a timestamp. Adding interval '1 month' to 31 January clamps to the last day of February.
• date_trunc('month', x) truncates to the start of the unit; on a date it returns timestamptz. to_char(x, 'YYYY-MM') formats for display.
• AT TIME ZONE converts an instant to the wall-clock time in a zone.

For ranges, use a half-open interval: at >= start AND at < next_start. BETWEEN '2024-01-01' AND '2024-12-31' on a timestamp means "up to midnight at the start of 31 December", so it drops almost all of the last day.

1CREATE TABLE events (id int PRIMARY KEY, at timestamp NOT NULL);2INSERT INTO events VALUES (1, '2024-12-31 00:00'), (2, '2024-12-31 18:30'), (3, '2025-01-01 00:00');

1SELECT date '2024-03-01' - date '2024-02-01'                 AS days_in_feb,2       (date '2024-01-31' + interval '1 month')::text        AS jan31_plus_month,3       (date '2024-01-31' + 30)::text                        AS jan31_plus_30,4       date_trunc('month', timestamp '2024-02-17 13:45')::text AS month_start,5       pg_typeof(date_trunc('month', date '2024-02-17'))    AS trunc_of_date_type,6       to_char(timestamp '2024-02-17 13:45', 'YYYY-MM')      AS year_month,7       (timestamptz '2024-03-10 12:00+00' AT TIME ZONE 'America/New_York')::text AS new_york_wall_clock,8       (SELECT string_agg(id::text, ',' ORDER BY id) FROM events9         WHERE at BETWEEN '2024-01-01' AND '2024-12-31')     AS between_ids,10       (SELECT string_agg(id::text, ',' ORDER BY id) FROM events11         WHERE at >= '2024-01-01' AND at < '2025-01-01')     AS half_open_ids;

Common mistakes

• BETWEEN with a date upper bound on a timestamp column, losing the last day of the range.
• Joining the calendar to a timestamp column with = (o.placed_at = d), which only matches orders placed exactly at midnight. Join on a range.
• INNER JOIN from the calendar to the facts, which deletes the empty days the calendar was there to produce.
• Storing event times as timestamp and then not knowing which zone a value was written in.

What interviewers ask

• timestamp versus timestamptz? timestamptz is an absolute instant stored as UTC and shown in the session zone; timestamp is an unanchored wall-clock value.
• How do you include days with no sales in a daily report? generate_series for the calendar, LEFT JOIN the facts, COALESCE(sum(...), 0).
• Why avoid BETWEEN for time ranges? It is inclusive at both ends; half-open ranges compose without overlaps or gaps.