2026-10-16 · 8 min read
- postgresql
- sql
- time
date_bin answers a narrower question than date_trunc. You give it a stride, a source timestamp, and an origin. It returns origin + N × stride for the integer N that puts the result on or before the source, as close as the stride allows. The next bucket begins one stride later.
The origin is not a clamp. A source earlier than the origin still returns a bucket, with N negative. For origin 2001-01-01 00:00:00, stride 15 minutes, and source 2000-12-31 23:50:00, the result is 2000-12-31 23:45:00.
Choose the origin in the same units as the chart
Quarter-hour charts want an origin whose minutes and seconds are zero, or any other origin that is a multiple of 15 minutes away from such a boundary. Origin 2001-01-01 00:00:00 does that. Origin 2001-01-01 00:02:00 bins 2020-02-11 15:44:17 to 15:32:00 instead of 15:30:00. Every bar in the chart then looks two minutes early, and the query is still correct for the origin it was given.
Write the origin in the query as a literal you can diff, not as a column that changes per row, unless each row is supposed to have its own alignment. A per-row origin makes the buckets incomparable.
- Match source and origin as both timestamp or both timestamptz.
- Cast both in one known zone if you must cross those types.
- Keep the stride positive and free of months and years.
- Store the origin in the report definition next to the stride.
Prefer date_trunc for calendar edges
The day part of a stride is 86,400 seconds. Across a daylight-saving change, local midnights are 23 or 25 hours apart, so a 1 day date_bin started at a local midnight will not stay on local midnight. date_trunc('day', timestamptz) uses the session TimeZone and does stay on local midnight. Month and year strides are rejected because those units do not have a fixed length.
Use date_bin for heartbeat windows, lease durations, and any series where the width must be a fixed number of seconds. Use date_trunc for calendar days, hours on the clock, and months. Mixing them in one chart produces a seam on the transition day that looks like missing data.
Test a source before the origin and one on the edge
Three fixtures catch most mistakes. One source lands in the middle of a bucket. One source equals a boundary, which must return itself, because the comparison is less than or equal. One source is earlier than the origin, which must still return a bucket rather than null or the origin.
Repeat the boundary fixture on a timestamptz value in a session whose TimeZone observes daylight saving, if the stride is a day or an hour. The UTC instant of local midnight is the value to store. The printed local time is not.
Compare bucket edges as Unix seconds
extract(epoch from a timestamptz bucket) is seconds since the Unix epoch. Truncate it to an integer before you compare it with a log line or a chart library that uses whole seconds. Do not multiply it back to milliseconds and then hand it to a tool that already expects seconds.
Format the SQL in Flashman's SQL formatter, which does not run it. Paste the integer seconds into the timestamp converter and read the UTC instant. Diff that instant with the label on the chart, and use the JSON formatter when the series arrives as an API payload. The origin and the stride belong in the ticket beside those two instants.