sqlsnowflake-cloud-data-platformnstimeinterval

Create table with 15 minutes interval on date time in Snowflake


I am trying to create a table in Snowflake with 15 mins interval. I have tried with generator, but that's not give in the 15 minutes interval. Are there any function which I can use to generate and build this table for couple of years worth data.

Such as

Date Hour
202-03-29 02:00 AM
202-03-29 02:15 AM
202-03-29 02:30 AM
202-03-29 02:45 AM
202-03-29 03:00 AM
202-03-29 03:15 AM
......... ........
......... ........

Thanks


Solution

  • Use following as time generator with 15min interval and then use other date time functions as needed to extract date part or time part in separate columns.

    with CTE as
    (select timestampadd(min,seq4()*15 ,date_trunc(hour, current_timestamp())) as time_count 
    from table(generator(rowcount=>4*24)))
    select time_count from cte;
    +-------------------------------+
    | TIME_COUNT                    |
    |-------------------------------|
    | 2022-03-29 14:00:00.000 -0700 |
    | 2022-03-29 14:15:00.000 -0700 |
    | 2022-03-29 14:30:00.000 -0700 |
    | 2022-03-29 14:45:00.000 -0700 |
    | 2022-03-29 15:00:00.000 -0700 |
    | 2022-03-29 15:15:00.000 -0700 |
    .
    .
    .
    ....truncated output
    | 2022-03-30 13:15:00.000 -0700 |
    | 2022-03-30 13:30:00.000 -0700 |
    | 2022-03-30 13:45:00.000 -0700 |
    +-------------------------------+