PostgreSQL Date Truncation and Ranges

Almost every analytics question in an interview turns into “group these events by day” or “how many orders last month”. The SQL looks trivial, and that’s why it’s a good filter: the answer that comes out quickly is usually off by one day, one time zone, or one index. This guide works through one dataset and the three places it goes wrong.

The table is a list of orders with a created_at timestamp. Seven rows, chosen so that the edges matter:

 id | created_at (UTC)
----+-------------------------
  1 | 2026-03-01 00:15:00
  2 | 2026-03-01 03:30:00
  3 | 2026-03-14 09:00:00
  4 | 2026-03-31 00:00:00
  5 | 2026-03-31 14:45:00
  6 | 2026-03-31 23:59:59.5
  7 | 2026-04-01 00:00:00

By UTC, six of those are March orders and one is April. Keep that number in mind.

Why BETWEEN gives the wrong count for March

Here’s the filter people write when asked for March:

SELECT count(*) FROM orders
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31';

On a timestamp column, PostgreSQL reads '2026-03-31' as midnight at the start of the 31st. BETWEEN is inclusive on both ends, so the filter means “up to and including 00:00:00 on March 31”. Row 4 is included, rows 5 and 6 aren’t. The count is 4, not 6, and the bug only shows on the last day of the period, which is exactly the day nobody spot-checks.

The usual patch is to extend the upper bound to '2026-03-31 23:59:59'. That gets 5. Row 6, at 23:59:59.5, still falls through the gap, because PostgreSQL timestamps carry microseconds. You could keep adding nines, or you could stop describing the end of the period and describe the start of the next one:

SELECT count(*) FROM orders
WHERE created_at >= '2026-03-01'
  AND created_at <  '2026-04-01';

That returns 6. It’s called a half-open range: closed at the start, open at the end. It has no gaps, it never double-counts row 7 (which belongs to April and would appear in both months if both ends were inclusive), and it works the same for days, weeks and quarters. For “the last 7 days” you write created_at >= now() - interval '7 days', and for “this month” created_at >= date_trunc('month', now()).

The three counts (4, 5 and 6) came from running the same comparisons in SQLite 3.53 against text timestamps, with the date-only literal written as '2026-03-31 00:00:00' to mirror the conversion PostgreSQL does. If you test in SQLite yourself, note that it compares the string '2026-03-31' as text, which sorts before '2026-03-31 00:00:00', so the bare BETWEEN returns 3 there.

Truncate in GROUP BY, keep the filter sargable

date_trunc(field, source) rounds a timestamp down to the start of a unit: 'day', 'week' (Monday), 'month', 'quarter', 'year', and smaller units down to microseconds. It’s what you want for buckets:

SELECT date_trunc('day', created_at) AS day,
       count(*)                      AS orders
FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
GROUP BY 1
ORDER BY 1;

Now look at a filter that uses the same function:

-- correct result, slow on a big table
WHERE date_trunc('day', created_at) = '2026-03-14'

It returns the right rows, but an ordinary B-tree index on created_at can’t serve it, because the index is sorted by created_at, not by the result of a function applied to it. PostgreSQL has to compute date_trunc for every row. The range form, created_at >= '2026-03-14' AND created_at < '2026-03-15', compares the bare column and turns into an index range scan. The jargon for this is “sargable” (search-argument-able), and interviewers often ask about it in exactly this shape.

You might think of adding an expression index on date_trunc('day', created_at). On a timestamptz column, PostgreSQL rejects it with functions in index expression must be marked IMMUTABLE, since the result depends on the session’s TimeZone setting. That’s a hint about the next problem.

For buckets that aren’t calendar units, such as 15-minute intervals for a latency chart, PostgreSQL 14 added date_bin(interval '15 minutes', created_at, timestamp '2026-01-01'), which rounds down to multiples of the stride from an origin you choose.

A day only exists in a time zone

Suppose the business runs in New York and the finance team wants monthly totals in local time. Convert the same seven UTC timestamps to America/New_York:

2026-03-01 00:15:00 -> 2026-02-28T19:15:00-05:00
2026-03-01 03:30:00 -> 2026-02-28T22:30:00-05:00
2026-03-14 09:00:00 -> 2026-03-14T05:00:00-04:00
2026-03-31 00:00:00 -> 2026-03-30T20:00:00-04:00
2026-03-31 14:45:00 -> 2026-03-31T10:45:00-04:00
2026-03-31 23:59:59.500 -> 2026-03-31T19:59:59.500000-04:00
2026-04-01 00:00:00 -> 2026-03-31T20:00:00-04:00

(That listing was produced with Python’s zoneinfo, using the same IANA time zone database PostgreSQL uses.) The first two orders were placed on the evening of February 28, local time. The last one, which UTC calls April, was placed on March 31 in New York. So the local count for March is 5, with 2 in February, while the UTC count is 6 and 1. Both are correct; they answer different questions. Notice also the offset changing from -05:00 to -04:00: daylight saving started on March 8, 2026, so “UTC minus five hours” isn’t a fixed rule you can hard-code.

With created_at stored as timestamptz, PostgreSQL 12 and later let you name the zone directly:

SELECT date_trunc('month', created_at, 'America/New_York') AS month,
       count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

On older versions, or if you’d rather get a local wall-clock value back, convert first: date_trunc('month', created_at AT TIME ZONE 'America/New_York'). That returns a timestamp without zone holding the local month start. The range filter needs the same care: local March starts at timestamptz '2026-03-01 00:00 America/New_York', which is 05:00 UTC.

If you omit the zone, the result depends on the TimeZone setting of whatever session ran the query. Your laptop, the reporting tool and the cron job can all disagree. Being explicit costs one argument.

A quick note on types, since interviewers like to ask: timestamptz stores an absolute instant (internally UTC) and displays it in the session zone. Plain timestamp stores a wall-clock reading with no zone at all. For events that happened at a moment, such as orders, logins and payments, timestamptz is almost always what you want.

Days with no orders don’t appear by themselves

GROUP BY only produces groups for values that exist. If no orders arrived on March 2, the result simply skips that date, and a line chart quietly draws a straight line across the gap. Generate the calendar and left join the data onto it:

SELECT d::date      AS day,
       count(o.id)  AS orders
FROM generate_series(timestamp '2026-03-01',
                     timestamp '2026-03-31',
                     interval '1 day') AS d
LEFT JOIN orders o
  ON o.created_at >= d
 AND o.created_at <  d + interval '1 day'
GROUP BY d
ORDER BY d;

Two details matter. count(o.id) counts non-null ids, so empty days show 0; count(*) would show 1 for them because the calendar row itself counts. And the join condition is the same half-open range as before, which keeps it index-friendly. (This query is PostgreSQL syntax; it isn’t part of the SQLite run above.)

Practice question: the dashboard that disagrees with finance

A daily revenue dashboard runs this query, and finance says March 31’s figure is too low and April 1’s is too high. Their numbers come from the payment provider’s New York-time export.

SELECT created_at::date AS day, sum(amount)
FROM payments
WHERE created_at BETWEEN '2026-03-01' AND '2026-04-30'
GROUP BY 1 ORDER BY 1;

What’s wrong, and how do you fix it?

Answer. The cast created_at::date truncates in the session’s time zone, which on the dashboard’s database connection is UTC. Payments made between 20:00 and midnight New York time on March 31 (00:00 to 04:00 UTC in daylight saving time) land on April 1, which matches the symptom exactly. Group in the business’s zone instead: date_trunc('day', created_at, 'America/New_York'), or (created_at AT TIME ZONE 'America/New_York')::date. The filter has its own bug: BETWEEN ... '2026-04-30' stops at midnight at the start of April 30 and drops that whole day. Replace it with created_at >= timestamptz '2026-03-01 00:00 America/New_York' AND created_at < timestamptz '2026-05-01 00:00 America/New_York'. A good answer also suggests confirming against a known day of the export before shipping, since the provider might be using a different zone than everyone assumes.

Further reading

• SQL interview questions and SQL MCQs
• Composite indexes, for when a range on time follows an equality on tenant
• Reading EXPLAIN ANALYZE to confirm the range scan
• PostgreSQL docs: date/time functions, including date_trunc and date_bin