Snowflake procedure with default datetime arguments

Question

I'm looking to create a Snowflake procedure that would enable end user to create a table with a list of timestamps within defined period of specific interval.

Example

The end-user passing arguments:

• from_date : CURRENT_DATE() - 2
• to_date : CURRENT_DATE
• diff_factor : hour

Should obtain table of 48 rows resembling the one below

ID	ts
1	Mon, 18 Dec 2023 21:15:46 GMT (Whatever equals CURRENT_DTE() - 2)
2	Mon, 18 Dec 2023 22:15:46 GMT
...	...

Code

Within the code below the procedure receives two DATETIME arguments and falls back on CURRENT_DATE if not provided.

CREATE OR REPLACE PROCEDURE generate_dates_table(
  from_date DATETIME DEFAULT CURRENT_DATE() - 1, 
  to_date DATETIME DEFAULT CURRENT_DATE,
  diff_factor STRING DEFAULT 'hour')
  RETURNS TABLE(id INTEGER, ts TIMESTAMP_NTZ)
  LANGUAGE SQL 
  COMMENT = 'Generate nice table with timestamps'
  EXECUTE AS CALLER
AS
DECLARE
  res RESULTSET
  difference INTEGER DEFAULT 1 ;
BEGIN
  LET difference := (SELECT(DATEDIFF(:diff_factor, :from_date, :to_date))); 
  LET res : = (SELECT (SEQ4) AS id,
               DATEADD(:diff_factor, SEQ4() , :from_date) AS ts
               FROM TABLE(GENERATOR(ROWCOUNT => :difference))));
RETURN TABLE(res); 
END


-- Test
CALL generate_dates_table();

Errors

• Snowflake returns multiple error messages when trying to compile statement, such as unexpected SELECT and SEQ4 statements

Notes

• I'm mostly interested in working through Snowflake scripting and learning:
  • What's the appropriate way to handle date time arguments with default values as expression
  • How to store results of a query as a variable to used later in scripting? difference should return number of rows that can be later used by GENERATOR and timestamp calculations
  • Are there any other suggestions concerning the script structure
• I'm not interested in non-procedural solutions generating list of dates or timestamps, like the nifty answer provided here

Answer 1

Composable UDTF:

CREATE OR REPLACE FUNCTION generate_dates_table(
  from_date    DATETIME DEFAULT CURRENT_DATE() - 1, 
  to_date      DATETIME DEFAULT CURRENT_DATE(),
  diff_factor  STRING   DEFAULT 'hour'
)
RETURNS TABLE(id INTEGER, ts TIMESTAMP_NTZ)
AS
$$
  SELECT value::INT, DATEADD('HOUR', value::INT, from_date)
  FROM TABLE(FLATTEN(ARRAY_GENERATE_RANGE(0, DATEDIFF('HOUR',from_date,to_date)+1)))
  WHERE diff_factor ILIKE 'hour'
  UNION ALL
  SELECT value::INT, DATEADD('DAY', value::INT, from_date)
  FROM TABLE(FLATTEN(ARRAY_GENERATE_RANGE(0, DATEDIFF('DAY',from_date,to_date)+1)))
  WHERE diff_factor ILIKE 'day' 
  -- more dayparts go here
$$

Sample call:

SELECT *
FROM TABLE(generate_dates_table());

SELECT *
FROM TABLE(generate_dates_table(diff_factor=>'DAY'));

SELECT *
FROM TABLE(generate_dates_table(from_date => '2023-12-05'::DATETIME,
                                to_date => '2023-12-19'::DATETIME,
                                diff_factor=>'DAY'));