CODEWITHSUNDEEP
sqldatetime-seriessnowflake-cloud-data-platformdate-range
kimi
kimi

Reputation: 525

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

Upvotes: 4

Views: 3800

Answers (3)

cbare
cbare

Reputation: 12468

Here's something slightly different from what the o.p. was asking for, but useful for those looking to generate a list of dates in Snowflake SQL.

This uses the row_number window function along with dateadd and generator to increment from a starting date.

select
  dateadd(day, row_number() over(order by 1)-1, to_date('2023-04-01')) as seq_date
from
  table(generator(rowcount => 30));

...resulting in:

SEQ_DATE
2023-04-01
2023-04-02
2023-04-03
...
2023-04-28
2023-04-29
2023-04-30

See also: Lukasz Szozda's answer to a similar question.

Upvotes: 0

leftjoin
leftjoin

Reputation: 38325

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

Upvotes: 7

Simon D
Simon D

Reputation: 6269

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   |
+--+----------+----------+-------------+

Upvotes: 2

Related Questions