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.

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

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.

AdditionDateTimestampNumberIntervalUnknownFloat

Date

INVALID

INVALID

Date + Interval day

Date + Interval IntervalUnit

DATEADD_UDF

DATEADD_UDF

Timestamp

INVALID

INVALID

Timestamp + Interval day

Timestamp + Interval IntervalUnit

DATEADD_UDF

DATEADD_UDF

Number

Date + Interval day

Timestamp + Interval day

Number + Number

INVALID

Number + Float

Interval

Date + Interval IntervalUnit

Timestamp + Interval IntervalUnit

INVALID

Unknown + Interval IntervalUnit

INVALID

Unknown

DATEADD_UDF

DATEADD_UDF

Unknown + Number

Unknown + Interval IntervalUnit

Float

DATEADD_UDF

DATEADD_UDF

Float + Number

INVALID

Float + Float

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

SELECT AsDate + AsDateTwo From TIMES;

SELECT AsDate + AsTimeStamp From TIMES;

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

IN -> Oracle_02.sql
SELECT AsDate + 1 FROM TIMES;

SELECT 1 + AsDate FROM TIMES;

Snowflake

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;

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

IN -> Oracle_03.sql
SELECT AsTimestamp + 1 FROM TIMES;

SELECT 1 + AsTimestamp FROM TIMES;

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

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