1

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

SQlOra
  • 13
  • 4
  • Can timstampadd be used and then iterated over to get desired rows. select timestampadd(min,45,current_timestamp()); – Pankaj Mar 29 '22 at 20:53

2 Answers2

1

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 |
+-------------------------------+
Pankaj
  • 2,692
  • 2
  • 6
  • 18
0

There are many answers to this question h e r e already (those 4 are all this month).

But major point to note is you MUST NOT use SEQx() as the number generator (you can use it in the ORDER BY, but that is not needed). As noted in the doc's

Important

This function uses sequences to produce a unique set of increasing integers, but does not necessarily produce a gap-free sequence. When operating on a large quantity of data, gaps can appear in a sequence. If a fully ordered, gap-free sequence is required, consider using the ROW_NUMBER window function.

CREATE TABLE table_of_2_years_date_times AS
SELECT 
    date_time::date as date,
    date_time::time as time
FROM (
    SELECT 
        row_number() over (order by null)-1 as rn
        ,dateadd('minute', 15 * rn, '2022-03-01'::date) as date_time
    from table(generator(rowcount=>4*24*365*2))
)
ORDER BY rn;

then selecting the top/bottom:

(SELECT * FROM table_of_2_years_date_times ORDER BY date,time LIMIT 5)
UNION ALL 
(SELECT * FROM table_of_2_years_date_times ORDER BY date desc,time desc LIMIT 5)
ORDER BY 1,2;
DATE TIME
2022-03-01 00:00:00
2022-03-01 00:15:00
2022-03-01 00:30:00
2022-03-01 00:45:00
2022-03-01 01:00:00
2024-02-28 22:45:00
2024-02-28 23:00:00
2024-02-28 23:15:00
2024-02-28 23:30:00
2024-02-28 23:45:00
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45