Datetime Arithmetic

This content explains the current transformation for some arithmetic operations between datetime types.

Description

In Oracle, some arithmetic operations could be performed between DateTime types, like addition, subtraction, multiplication, and division. Currently, SnowConvert can resolve some cases of addition and subtraction. These cases are explained below.

Sample Source Patterns

This is a summary of the current transformation for the different combinations of the addition and subtraction operations with date, timestamps, number, and unknown types.

Consider the next table for the examples below.

Oracle

IN -> Oracle_01.sql

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

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

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE TIMES (
 AsTimeStamp TIMESTAMP(6),
 AsTimestampTwo TIMESTAMP(6),
 AsDate TIMESTAMP(6),
 AsDateTwo TIMESTAMP(6)
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

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

Addition

Combination Matrix

This is a summary of how the migrator resolves the addition operations for the different combinations with date, timestamps, number, and unknown types.

An Unknown Type column is the result of the migrator being unable to establish the data type that the column contains. This can happen for many reasons, for example missing DDLs for the tables being operated on, columns resulting from operations on views, CTES or subqueries, etc.

By default, Snow Convert migrates operations of type Date/Timestamp + Interval to the native Snowflake operations, but in some cases may be useful to use UDF instead. Further details about this UDF can be found here.

The different paths that the migrator can use for resolving the add operations will be explained below:

Invalid

Certain combinations are not valid to perform addition operations in Oracle:

Oracle

Query

SELECT AsDate + AsDateTwo From TIMES;

SELECT AsDate + AsTimeStamp From TIMES;

Result

SQL Error [975] [42000]: ORA-00975: date + date not allowed

SQL Error [30087] [99999]: ORA-30087: Adding two datetime values is not allowed

Date + Interval day

This is the current transformation for the addition operation between a date type and a number (and vice versa). For example

Oracle

Query

IN -> Oracle_02.sql

SELECT AsDate + 1 FROM TIMES;

SELECT 1 + AsDate FROM TIMES;

Result

|ASDATE+1               |
|-----------------------|
|2021-11-07 00:00:00.000|

|1+ASDATE               |
|-----------------------|
|2021-11-07 00:00:00.000|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
 AsDate + 1 FROM
 TIMES;


SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Number AND unknown ***/!!! 1 + AsDate FROM
 TIMES;

Result

|ASDATE + INTERVAL '1 DAY'|
|-------------------------|
|2021-11-07               |

Timestamp + Interval day

This is the current transformation for the addition operation between a timestamp type and a number (and vice versa). For example

Oracle

Query

IN -> Oracle_03.sql

SELECT AsTimestamp + 1 FROM TIMES;

SELECT 1 + AsTimestamp FROM TIMES;

Result

|ASTIMESTAMP+1          |
|-----------------------|
|2021-11-06 11:00:00.000|

|1+ASTIMESTAMP          |
|-----------------------|
|2021-11-06 11:00:00.000|

Note: In Oracle, both DATE and TIMESTAMP columns contain a time component, but Oracle has used the format mask specified by the NLS_DATE_FORMAT parameter to decide how to implicitly convert the date to a string, that is why when performing some operations between TIMESTAMP and Intervals, he result could be shown as DATE, hiding the time component, unless the NLS_DATE_FORMAT parameter is changed.

Snowflake