当前位置：文江博客话题详情

SQL nstimeinterval snowflake-cloud-data-platform

在 Snowflake 中创建日期时间间隔为 15 分钟的表

发布于 2025-01-18 00:36:54 字数 1164 浏览 3 评论 0 原文

我试图以15分钟的间隔在雪花上创建一张桌子。我已经尝试使用发电机，但这并不是在15分钟内给出的。我可以使用任何功能来生成和构建该表数年，值得数据。

例如

日期	小时
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	AM
03：15	............................................................................................

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光磨忆 2025-01-25 00:36:54

使用以下作为间隔为 15 分钟的时间生成器，然后根据需要使用其他日期时间函数在单独的列中提取日期部分或时间部分。

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

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

回复收藏 0 原文

醉生梦死 2025-01-25 00:36:54

这个问题有很多答案 e < /a> e 已经（这4个都是这个月）。

但是要注意的主要点是，您不得将 seqx（）用作数字生成器（您可以按顺序使用它，但不需要）。如 doc

重要

此函数使用序列来产生一组唯一的增加整数集，但不一定会产生无间隙序列。当用大量数据运行时，差距可以以序列出现。如果需要完全有序的，需要无间隙序列，请考虑使用Row_number窗口函数。

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;

选择顶部/底部：

(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;

时间	2022-03-01
00:00:00	2022-03-01
00:15:00	2022-03-03-01 00:30:00
2022-03-03-03-01	00:45:00
202222222222	然后
日期 -03-01	01：00
2024-02-28	22:45:00
2024-02-28	23:00:00
2024-02-28	23:15:00
2024-02-28	23:00
2024 2024 -02-28	23:45:00

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

回复收藏 0 原文

仙女 2025-01-25 00:36:54

Snowflake 具有TIME_SLICE 函数。请参阅文档

计算时间“片段”的开始或结束，其中片段的长度是标准时间单位（分钟、小时、天等）的倍数。< /p>

取决于您的计算方式想要转换它们，您可以使用 START 或 END slice。

SELECT 
    '2024-06-28T01:23:45.678'::TIMESTAMP_NTZ AS timestamp_1,
    '2024-06-28T01:31:45.678'::TIMESTAMP_NTZ AS timestamp_2,
    TIME_SLICE(timestamp_1, 15, 'MINUTE') AS slice_for_timestamp_1,
    TIME_SLICE(timestamp_2, 15, 'MINUTE') AS slice_for_timestamp_2

输出将如下所示

TIMESTAMP_1	TIMESTAMP_2	SLICE_FOR_TIMESTAMP_1	SLICE_FOR_TIMESTAMP_2
2024-06-28 01:23:45.678	2024-06-28 01:31:45.678	2024-06-28 01:15:00.000	2024-06-28 01:30:00.000

Snowflake has the function TIME_SLICE. See documentation

Calculates the beginning or end of a “slice” of time, where the length of the slice is a multiple of a standard unit of time (minute, hour, day, etc.).

Depending on how you want to convert them you can use either START or END slice.

SELECT 
    '2024-06-28T01:23:45.678'::TIMESTAMP_NTZ AS timestamp_1,
    '2024-06-28T01:31:45.678'::TIMESTAMP_NTZ AS timestamp_2,
    TIME_SLICE(timestamp_1, 15, 'MINUTE') AS slice_for_timestamp_1,
    TIME_SLICE(timestamp_2, 15, 'MINUTE') AS slice_for_timestamp_2

The output will looks as follows