Search code examples
sqldatetime-seriessnowflake-cloud-data-platformdate-range

Generate date range in Snowflake

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:

enter image description here

Solution

  • 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