TIMESTAMP DIFF UDF
Description
Snowflake does not support the addition operation between TIMESTAMP
data types with the -
operand. In order to replicate this functionality, we have added a custom UDF.
Custom UDF overloads
TIMESTAMP_DIFF_UDF(timestamp, timestamp)
Parameters
LEFT_TS: The first
TIMESTAMP
of the operation.RIGHT_TS: The
TIMESTAMP
to be added.
CREATE OR REPLACE FUNCTION TIMESTAMP_DIFF_UDF(LEFT_TS TIMESTAMP, RIGHT_TS TIMESTAMP )
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH RESULTS(days,hours,min,sec,millisecond,sign) AS
(
SELECT
abs(TRUNC(x/1000/3600/24)) days,
abs(TRUNC(x/1000/60 / 60)-trunc(x/1000/3600/24)*24) hours,
abs(TRUNC(MOD(x/1000,3600)/60)) min,
abs(TRUNC(MOD(x/1000,60))) sec,
abs(TRUNC(MOD(x,1000))) millisecond,
SIGN(x)
FROM (SELECT TIMESTAMPDIFF(millisecond, RIGHT_TS, LEFT_TS) x ,SIGN(TIMESTAMPDIFF(millisecond, RIGHT_TS, LEFT_TS)) sign))
SELECT
IFF(SIGN>0,'+','-') || TRIM(TO_CHAR(days,'000000000')) || ' ' || TO_CHAR(hours,'00') || ':' || TRIM(TO_CHAR(min,'00')) || ':' || TRIM(TO_CHAR(sec,'00')) || '.' || TRIM(TO_CHAR(millisecond,'00000000'))
from RESULTS
$$;
Oracle
--Create Table
CREATE TABLE timestampdiff_table (col1 TIMESTAMP, col2 TIMESTAMP);
--Insert data
INSERT INTO timestampdiff_table VALUES ('2000-03-20 22:01:11', '1996-03-20 10:01:11');
INSERT INTO timestampdiff_table VALUES ('1996-03-20 22:01:11', '2000-03-20 10:01:11');
INSERT INTO timestampdiff_table VALUES ('1982-05-11 22:31:19', '1900-01-25 15:21:15');
INSERT INTO timestampdiff_table VALUES ('1999-12-25 01:15:16', '1900-12-11 02:05:16');
--Select
SELECT col1 - col2 FROM timestampdiff_table;
Snowflake
--Create Table
CREATE OR REPLACE TABLE timestampdiff_table (col1 TIMESTAMP(6),
col2 TIMESTAMP(6)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Insert data
INSERT INTO timestampdiff_table
VALUES ('2000-03-20 22:01:11', '1996-03-20 10:01:11');
INSERT INTO timestampdiff_table
VALUES ('1996-03-20 22:01:11', '2000-03-20 10:01:11');
INSERT INTO timestampdiff_table
VALUES ('1982-05-11 22:31:19', '1900-01-25 15:21:15');
INSERT INTO timestampdiff_table
VALUES ('1999-12-25 01:15:16', '1900-12-11 02:05:16');
--Select
SELECT
PUBLIC.TIMESTAMP_DIFF_UDF( col1, col2) FROM
timestampdiff_table;
Known Issues
1. TIMESTAMP format may differ from Oracle
The TIMESTAMP
format may differ from Oracle, please consider the TIMESTAMP_OUTPUT_FORMAT
setting when working with TIMESTAMP
data types.
Related EWIs
No related EWIs.
Last updated