snowflake-cloud-data-platformsnowflake-schema

how to add Interval time in snowflake using a parameter


We have a use case where user will provide starttime, endtime, and Interval as parameters. and we need to pass these parameters to view logic where we have business logic, But the Interval function is not accepting any parameters. Please help how to pass parameter to add interval. below is a simple example.

set v_StartTime = to_timestamp_ntz('2024-04-01 00:00:00');

set v_EndTime = to_timestamp_ntz('2024-04-02 00:00:00');

set v_Interval = '1 HOUR';

Below Statement works in snowflake:

SELECT $v_StartTime, $v_StartTime + INTERVAL '1 HOUR' , $v_EndTime;

Below Statement is not working while using $v_Interval as parameter:

SELECT $v_StartTime, ($v_StartTime + INTERVAL $v_Interval ) , $v_EndTime;

Solution

  • You can use seperate parameters for interval amount and type and feed that to dateadd

    set v_StartTime = to_timestamp_ntz('2024-04-01 00:00:00');
    set v_EndTime = to_timestamp_ntz('2024-04-02 00:00:00');
    set v_Interval = 1;
    set v_IntervalType = 'hour';
    
    select $v_StartTime, dateadd($v_IntervalType, $v_Interval, $v_StartTime), $v_EndTime;