How To: Generating Simple Timeline Records in Snowflake

When you need to segment (a.k.a. fanning out) your events into 10 second grain (Or whatever grain you need), you will need to generate new records representing that grain across time.   This function enables you to do just that and join again your event table.   In the join it is best to use a form of overlapping window join.   Please look at my recent post on what that logic looks like.  

CREATE OR REPLACE FUNCTION GENERATE_TIMELINE(ARG_START_TIME STRING, ARG_END_TIME STRING, ARG_GRAIN INTEGER)
  RETURNS TABLE ( TIME TIMESTAMP_NTZ, TIME_START TIMESTAMP_NTZ, TIME_END TIMESTAMP_NTZ )
  AS 
 $$
            SELECT * 
              FROM (
                  SELECT DATEADD(SECOND, ((ROW_NUMBER() OVER(ORDER BY 1)) - 1) * ARG_GRAIN, ARG_START_TIME::TIMESTAMP_NTZ) AS TIME
                         , TIME AS TIME_START
                         , DATEADD(SECOND, ARG_GRAIN-1, TIME) AS TIME_END
                     FROM TABLE(generator(rowcount => 1000000))
                    )
              WHERE TIME BETWEEN ARG_START_TIME AND ARG_END_TIME  
 $$;
 

SELECT *
  FROM TABLE(GENERATE_TIMELINE('2021-06-01 05:55:03','2021-06-01 07:05:15',10))