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

1. LEFT_TS: The first TIMESTAMP of the operation.

2. RIGHT_TS: The TIMESTAMP to be added.

UDF

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

Query

IN -> Oracle_01.sql

--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;

Result

COL1-COL2      |
---------------+
1461 12:0:0.0  |
-1460 12:0:0.0 |
30056 7:10:4.0 |
36172 23:10:0.0|

Snowflake

Query

OUT -> Oracle_01.sql

--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;

Result

TIMESTAMP_DIFF_UDF( COL1, COL2)|
-------------------------------+
+000001461  12:00:00.00000000  |
-000001460  12:00:00.00000000  |
+000030056  07:10:04.00000000  |
+000036172  23:10:00.00000000  |

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.