Snowflake: Populate a table with minute increments between two timestamps

Question

In Snowflake SQL, I would like to g generate a table with one column where the minimum value is the min(truncated timestamp) of another table and the maximum value is the max(truncated timestamp) of the same table.

i.e.:

table1:

| 2022-01-01 00:00:05 |
| 2022-01-01 00:05:10 |
| 2022-01-01 00:10:15 |
| 2022-01-01 00:15:20 |
| 2022-01-01 00:20:25 |

expected output:

| 2022-01-01 00:00:00 |
| 2022-01-01 00:01:00 |
| 2022-01-01 00:02:00 |
|         ...         |
| 2022-01-01 00:20:00 |
| 2022-01-01 00:21:00 |

I found this Snowflake article but it requires executing multiple queries. I would like to achieve this using a CTE.

I'm using this code block as a startpoint.

WITH params AS (
    SELECT
        date_trunc(MINUTE, min(dt)) AS st_time,
        datediff(
              MINUTE, 
              date_trunc(MINUTE, min(dt)), 
              date_trunc(MINUTE, max(dt))
          ) + 1 AS mins
    FROM table1
)

SELECT
    DATEADD(MINUTE, SEQ4(), date_trunc(MINUTE, params.st_time))
FROM TABLE(generator(rowcount => (SELECT avg(mins) FROM params) )), params;

I'd appreciate any help at all!

I've tried the Snowflake help guide but I want to avoid executing multiple queries. I usually get the error: Generator ROWCOUNT must be constant.

Answer 1

You can use either a recursive CTE or a procedural UDTF to generate a specific number of rows without needing a constant value (as the generator() table function requires).

Recursive CTE:

with TABLE1 as
(
    select 
    COLUMN1::timestamp as "COL1"
    from (values
    ('2022-01-01 00:05:10'),
    ('2022-01-01 00:10:15'),
    ('2022-01-01 00:15:20'),
    ('2022-01-25 00:20:25'))
), MIN_MAX as
(
select   (select date_trunc(minute, min(COL1)) from TABLE1) as MIN_TS
        ,(select date_trunc(minute, max(COL1)) from TABLE1) as MAX_TS
        ,datediff(minutes, MIN_TS, MAX_TS)                  as TOTAL_MINUTES
), RECURSIVE as
(
select MIN_TS, MAX_TS from MIN_MAX
union all
select dateadd(minute, 1, MIN_TS), MAX_TS from RECURSIVE where MIN_TS < MAX_TS
)
select MIN_TS as TS from RECURSIVE
;

Procedural UDTF:

create or replace function GENERATE_MINUTES(MIN_TIME timestamp, MAX_TIME timestamp)
returns table(TS timestamp)
language javascript
as
$$
{
    processRow: function (row, rowWriter, context) {
        const ms = 60 * 1000;
        minTime = new Date(Math.floor(row.MIN_TIME.getTime() / ms) * ms);
        maxTime = new Date(Math.ceil(row.MAX_TIME.getTime() / ms) * ms);
        curTime = minTime;
        while (curTime <= maxTime) {
            rowWriter.writeRow({TS:curTime});
            curTime = new Date(curTime.getTime() + ms);
        }
    },
}
$$;

Then you can just select the min and max in a with clause and pass the values into the UDTF to create the rows:

with TABLE1 as
(
    select 
    COLUMN1::timestamp as "COL1"
    from (values
    ('2022-01-01 00:05:10'),
    ('2022-01-01 00:10:15'),
    ('2022-01-01 00:15:20'),
    ('2022-01-01 00:20:25'))
), MIN_MAX as
(
select   (select min(COL1) from TABLE1) as MIN_TS
        ,(select max(COL1) from TABLE1) as MAX_TS
)
select TS from MIN_MAX, table(generate_minutes(MIN_TS, MAX_TS))
;

This uses the CEIL function on the max time, which means that it will round up to the nearest minute. If you don't want that you can change it to use ROUND or FLOOR.