How to generate a time or date series

Help Center
1

Firebolt doesn't currently have a "time_series" function, but using a Lambda function it is nonetheless possible to generate a time or date series.

To generate a list of timestamps

Choose the time you want to start with, and the interval you want the series to continue with and plug them into the query below: 

SELECT TRANSFORM(x -> DATE_ADD('<interval>',x,'<startdatetime>'::TIMESTAMP),ARRAY_ENUMERATE(SPLIT('',REPEAT('1',86400)))) as time_series

For example, this query generates a time series at second intervals, starting at 2022-04-25 08:56:05:

SELECT TRANSFORM(x -> DATE_ADD('second',x,'2022-04-25 08:56:05'::TIMESTAMP),ARRAY_ENUMERATE(SPLIT('',REPEAT('1',86400)))) as time_series

Screenshot_2023-03-17_at_2.41.48_PM.png

To generate a list of days:

Choose the date you want to start with, and the interval you want the series to continue with and plug them into the query below: 

SELECT TRANSFORM(x -> DATE_ADD('<interval>',x,'<startdate>'::DATE),ARRAY_ENUMERATE(SPLIT('',REPEAT('1',3)))) as date_series

For example, this query generates a date series at day intervals, starting at 2023-02-01:

SELECT TRANSFORM(x -> DATE_ADD('day',x,'2023-02-01'::DATE),ARRAY_ENUMERATE(SPLIT('',REPEAT('1',3)))) as date_series