Generate date range in Snowflake

Question

I want to create a date range between two timestamps. I saw similar posts and also checked this method. However, still couldn't achieve the expected output below.

Please note that if ended_at is NULL, then CURRENT_TIMESTAMP needs to be taken.

Example data:

WITH t1 AS (
SELECT 'A' AS id, '2021-05-18 18:30:00'::timestamp AS started_at, '2021-05-19 09:45:00'::timestamp AS ended_at UNION ALL
SELECT 'B' AS id, '2021-05-24 11:30:40'::timestamp AS started_at, NULL::timestamp AS ended_at
    )
SELECT *
FROM t1

Expected result:

leftjoin · Accepted Answer · 2021-05-25T15:09:39.307

Generate array of spaces with length = datedfiff, split array and flatten to generate rows. Use index as number of days to add to the start date:

WITH t1 AS (
SELECT 'A' AS id, '2021-05-18 18:30:00'::timestamp AS started_at, '2021-05-19 09:45:00'::timestamp AS ended_at UNION ALL
SELECT 'B' AS id, '2021-05-24 11:30:40'::timestamp AS started_at, NULL::timestamp AS ended_at
    )
    
SELECT t1.*, dateadd(day, v.index, to_date(t1.started_at)) as date_generated 
FROM t1, 
     lateral flatten (split(space(datediff(day,to_date(t1.started_at), nvl(to_date(t1.ended_at), current_date))),' ')) v
;

Result:

ID  STARTED_AT              ENDED_AT                DATE_GENERATED
A   2021-05-18 18:30:00.000 2021-05-19 09:45:00.000 2021-05-18
A   2021-05-18 18:30:00.000 2021-05-19 09:45:00.000 2021-05-19
B   2021-05-24 11:30:40.000 null                    2021-05-24
B   2021-05-24 11:30:40.000 null                    2021-05-25

@KadirSenkaya Edited: row_number is not needed because flatten gives index column — leftjoin, May 25 '21 at 16:23

score 2 · Answer 2 · answered May 25 '21 at 11:18

You can use a recursive CTE if you have a relatively small number of days you need to generate:

WITH t1 AS (
    SELECT
        'A'                              AS id,
        '2021-05-18 18:30:00'::timestamp AS started_at,
        '2021-05-19 09:45:00'::timestamp AS ended_at
    UNION ALL
    SELECT
        'B'                              AS id,
        '2021-05-24 11:30:40'::timestamp AS started_at,
        NULL::timestamp                  AS ended_at
),
     row_gen (id, started, ended, generated_day) as (
         select
             t1.id id,
             t1.started_at::date started,
             coalesce(t1.ended_at, current_timestamp)::date ended,
             started generated_day
         from t1
         union all
         select
             id,
             started,
             ended,
             dateadd('day', 1, generated_day)
         from row_gen
         where generated_day < ended
     )
SELECT *
FROM row_gen

+--+----------+----------+-------------+
|ID|STARTED   |ENDED     |GENERATED_DAY|
+--+----------+----------+-------------+
|A |2021-05-18|2021-05-19|2021-05-18   |
|A |2021-05-18|2021-05-19|2021-05-19   |
|B |2021-05-24|2021-05-25|2021-05-24   |
|B |2021-05-24|2021-05-25|2021-05-25   |
+--+----------+----------+-------------+

Generate date range in Snowflake

2 Answers2