2026-10-16 · 6 min read · Rahul Chitturi
- postgresql
- sql
- time
date_bin(stride, source, origin) returns the greatest instant of the form origin + N × stride that is still less than or equal to source. N may be negative. The origin is an alignment anchor, not the earliest timestamp you are willing to return.
A 15-minute stride with origin 2001-01-01 00:00:00 bins 2020-02-11 15:44:17 to 2020-02-11 15:30:00. Move that origin to 2001-01-01 00:02:00 and the same source bins to 15:32:00. The buckets are still 15 minutes wide. They no longer sit on :00, :15, :30, and :45.
Keep the stride in fixed microseconds
PostgreSQL rejects a stride whose month component is non-zero, and it rejects a stride that is zero or negative. The day component counts as 86,400 seconds. A stride of 1 day is exactly 24 hours, including across a daylight-saving transition. It is not a civil day.
date_trunc('day', source) follows the calendar in the session TimeZone when source is timestamptz. date_bin does not. Use date_trunc when the contract is local midnight. Use date_bin when the contract is a fixed duration aligned to an origin you chose. source and origin must be the same type. A cast between timestamp and timestamptz applies the session zone, so cast both sides the same way before you compare environments.
- Write the origin next to the query, not only the stride.
- Check a source earlier than the origin. The bucket can precede it.
- Do not pass a month or year stride.
- Compare timestamptz results as instants, not as printed wall time.
Compare the edge in Unix seconds
A chart that expects quarter hours and a query whose origin is offset by two minutes will disagree on every bucket, and both sides can be internally consistent. Log the origin, the stride, and one source that fell in the wrong bar.
Flashman's SQL formatter lays the call out. It does not execute date_bin. The timestamp converter takes Unix seconds, not milliseconds: take the integer part of extract(epoch from the bucket) and paste that. Diff the logged edge against the edge the chart used. The JSON formatter helps when those edges are buried in an API series.