date_bin function - Materialize

date_bin returns the largest value less than or equal to source that is a multiple of stride starting at origin––for shorthand, we call this "binning."

For example, on this number line of abstract units:

nofmt

          x
...|---|---|---|...
   7   8   9   10

With a stride of 1, we would have bins (...7, 8, 9, 10...).

Here are some example results:

`source`	`origin`	`stride`	Result
8.75	1	1 unit	8
8.75	1	2 units	7
8.75	1.75	1.5 units	7.75

date_bin is similar to date_trunc, but supports arbitrary strides, rather than only unit times.

Signatures

{{% include-syntax file="examples/sql_functions/date_bin" example="syntax" %}}

Parameter	Type	Description
stride	`interval`	Define bins of this width.
source	`timestamp`, `timestamp with time zone`	Determine this value's bin.
origin	Must be the same as source	Align bins to this value.

Return value

date_bin returns the same type as source.

Details

origin and source cannot be more than 2^63 nanoseconds apart.
stride cannot contain any years or months, but e.g. can exceed 30 days.
stride only supports values between 1 and 9,223,372,036 seconds.

Examples

mzsql

SELECT
  date_bin(
    '15 minutes',
    timestamp '2001-02-16 20:38:40',
    timestamp '2001-02-16 20:05:00'
  );

nofmt

      date_bin
---------------------
 2001-02-16 20:35:00

mzsql

SELECT
  str,
  "interval",
  date_trunc(str, ts)
    = date_bin("interval"::interval, ts, timestamp '2001-01-01') AS equal
FROM (
  VALUES
  ('week', '7 d'),
  ('day', '1 d'),
  ('hour', '1 h'),
  ('minute', '1 m'),
  ('second', '1 s')
) intervals (str, interval),
(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);

nofmt

  str   | interval | equal
--------+----------+-------
 day    | 1 d      | t
 hour   | 1 h      | t
 week   | 7 d      | t
 minute | 1 m      | t
 second | 1 s      | t