0

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?

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325

1 Answers1

1

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.

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • Looks similar to approach I suggested for generating date range - [generate_series() equivalent in snowflake](https://stackoverflow.com/a/76141254/5070879) :) – Lukasz Szozda Jun 12 '23 at 07:33
  • Indeed! I had another solution there, but I need hours this time :) https://stackoverflow.com/a/66449180/132438 (so the math could not just use `+`) – Felipe Hoffa Jun 13 '23 at 00:53