Interval UDFs vs Snowflake native interval operation

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

The following table shows a comparison between the DATEADD_UDF INTERVAL and DATEDIFF_UDF INTERVAL vs the Snowflake native operation for interval arithmetic.

Necessary Code

To run the queries of the comparative table it is necessary to run the following code:

Oracle

IN -> Oracle_01.sql

CREATE OR REPLACE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

INSERT INTO TIMES VALUES (
  TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
  TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
  TO_DATE('06/11/21', 'dd/mm/yy'), 
  TO_DATE('05/11/21', 'dd/mm/yy'));

CREATE TABLE UNKNOWN_TABLE(
  Unknown timestamp
);

INSERT INTO UNKNOWN_TABLE VALUES (
  TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);

Snowflake

Comparison Table

Oracle Queries

SELECT AsTimeStamp+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp-INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate+INTERVAL '10' DAY FROM TIMES;
SELECT AsDate-INTERVAL '10' DAY FROM TIMES;
SELECT AsDate+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate-INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT Unknown+INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+ AsTimeStamp FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsDate FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 1' DAY TO HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+AsDate FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '10' DAY+AsTimeStamp FROM TIMES;
SELECT INTERVAL '10' DAY+AsDate FROM TIMES;
SELECT INTERVAL '10' DAY+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5' HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5' HOUR+AsDate FROM TIMES;
SELECT INTERVAL '5' HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsDate FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '30' MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '30' MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '30' MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '333' HOUR(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+AsDate FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsDate FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+Unknown FROM UNKNOWN_TABLE;

Snowflake Operation

Snowflake UDF

Results

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior