We can generate a datetime array with specified bin-width using:
select generate_series(
timestamp without time zone '2020-10-01 00:00:00', '2020-10-04 00:00:00',
'24 hours') as ts
ts
1 2020-10-01
2 2020-10-02
3 2020-10-03
4 2020-10-04
Is it possible to generate an array of set length i.e. a given number of bins/breaks?
I want to provide a date range and number of equal intervals to divide it into.
From the comments:
I want to provide a date range and number of equal intervals to divide it into
You can use generate_series()
with integers, and date arithmetics:
with params as (
select
timestamp '2020-10-01' ts_start,
timestamp '2020-10-04' ts_end,
3 num
)
select ts_start + (ts_end - ts_start) * i / num as ts
from params
cross join lateral generate_series(0, num) s(i)
This splits the given time range into 3 intervals (resulting in a total of 4 timestamps).
| ts | | :------------------ | | 2020-10-01 00:00:00 | | 2020-10-02 00:00:00 | | 2020-10-03 00:00:00 | | 2020-10-04 00:00:00 |