Materialize Documentation
s
Join the Community github/materialize

date_bin_hopping Function

EXPERIMENTAL! The date_bin_hopping function is under construction and requires experimental mode. Available since v0.19.0.

date_bin_hopping returns every “binned” value:

date_bin_hopping provides a primitive operation to express what are referred to as “hopping windows” in other systems.

Signatures

date_bin_hopping ( hop , width , source , origin )
Parameter Type Description
hop interval Define bins of this width.
width interval Produce an “oldest” bin at the equivalent to date_bin(hop, source + hop - width).
source timestamp, timestamp with time zone Determine this value’s bins; produce a “newest” bin at the equivalent to date_bin(hop, source).
origin Must be the same as source Align bins to this value. If not provided, defaults to the Unix epoch.

Return value

date_bin_hopping returns the same type as source.

Details

Examples

SELECT * FROM date_bin_hopping('45s', '1m', TIMESTAMP '2001-01-01 00:01:20');
  date_bin_hopping
---------------------
 2001-01-01 00:00:30
 2001-01-01 00:01:15
SELECT date_bin_hopping AS timeframe_start, sum(v)
  FROM ( VALUES
    (TIMESTAMP '2021-01-01 01:05', 41),
    (TIMESTAMP '2021-01-01 01:07', 21),
    (TIMESTAMP '2021-01-01 01:09', 51),
    (TIMESTAMP '2021-01-01 01:11', 31),
    (TIMESTAMP '2021-01-01 01:13', 11),
    (TIMESTAMP '2021-01-01 01:17', 61)
  ) t (ts, v),
  date_bin_hopping(INTERVAL '5m', INTERVAL '10m', t.ts)
GROUP BY timeframe_start
ORDER BY 1;
   timeframe_start   | sum
---------------------+-----
 2021-01-01 01:00:00 | 113
 2021-01-01 01:05:00 | 155
 2021-01-01 01:10:00 | 103
 2021-01-01 01:15:00 |  61
Did this info help?
Yes No