How to generate all hours between 2 timestamps in Snowflake?

huangapple go评论62阅读模式
英文:

How to generate all hours between 2 timestamps in Snowflake?

问题

我有一张表格,其中包含多个时间戳,并且我想在Snowflake中生成最大时间戳和最小时间戳之间的所有时间戳 - 每隔1小时一次。我应该如何做?

英文:

I have a table with multiple timestamps, and I want to generate all the timestamps between the max and the min - in intervals of 1 hour with Snowflake.

How can I do this?

答案1

得分: 1

如果您有一个名为 for_stacko_timeline 的表,其中包含一个名为 ts 的列,您可以查找最小和最大时间戳。然后使用新函数 array_generate_range() 生成一个数字列表,用于在创建完整时间序列时进行迭代:

select timestampadd(hour, value, start_hour) generated_hour
from (
    select start_hour, array_generate_range(0, 1+timestampdiff(hour, start_hour, end_hour)) int_array
    from (
        select date_trunc(hour, min(ts)) start_hour, max(ts) end_hour 
        from for_stacko_timeline
    )
), table(flatten(int_array))

然后,您可以使用小时列表进行 left join,以在需要生成值为 0 而不是不存在行的情况下使用。

英文:

If you have a table for_stacko_timeline with a column ts, you can find the minimum and maximum timestamp. Then use the new function array_generate_range() to generate a list of numbers to iterate over while creating the full time series:

select timestampadd(hour, value, start_hour) generated_hour
from (
    select start_hour, array_generate_range(0, 1+timestampdiff(hour, start_hour, end_hour)) int_array
    from (
        select date_trunc(hour, min(ts)) start_hour, max(ts) end_hour 
        from for_stacko_timeline
)), table(flatten(int_array))

Then you can use that list of hours to do a left join in cases where you need to generate rows with 0 instead of non-existing rows.

huangapple
  • 本文由 发表于 2023年6月12日 13:18:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76453795.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定