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

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.

Addition

Date

Timestamp

Number

Interval

Unknown

Float

Date

INVALID

Date + Interval day

Date + Interval IntervalUnit

DATEADD_UDF

Timestamp

INVALID

Timestamp + Interval day

Timestamp + Interval IntervalUnit

DATEADD_UDF

Number

Date + Interval day

Timestamp + Interval day

Number + Number

INVALID

MSCEWI3036

Number + Float

Interval

Date + Interval IntervalUnit

Timestamp + Interval IntervalUnit

INVALID

MSCEWI3116

Unknown + Interval IntervalUnit

INVALID

Unknown

DATEADD_UDF

Unknown + Number

Unknown + Interval IntervalUnit

MSCEWI3036

Float

DATEADD_UDF

Float + Number

INVALID

MSCEWI3036

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;

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

IN -> Oracle_02.sql

SELECT AsDate + 1 FROM TIMES;

SELECT 1 + AsDate FROM TIMES;

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

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

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;

|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

IN -> Oracle_03.sql

SELECT AsTimestamp + 1 FROM TIMES;

SELECT 1 + AsTimestamp FROM TIMES;

|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

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;


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

|ASTIMESTAMP + INTERVAL '1 DAY'|
|------------------------------|
|2021-11-06 11:00:00.000       |

DATEADD_UDF

For those cases where there is an addition operation between a date or timestamp type and an unknown type, a user-defined function (UDF) is added. The current UDF implementation can be found here. The UDF is located in the UDFs folder. For example:

For the following examples, a subquery will be used, trying to simulate the Unknown Type column

Oracle

IN -> Oracle_04.sql

SELECT AsDate + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM TIMES) FROM TIMES;

SELECT AsTimestamp + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM TIMES) FROM TIMES;

|ASDATE+(SELECTEXTRACT(DAYFROMASTIMESTAMPTWO)FROMTIMES)|
|------------------------------------------------------|
|2021-11-11 00:00:00.000                               |

|ASTIMESTAMP+(SELECTEXTRACT(DAYFROMASTIMESTAMPTWO)FROMTIMES)|
|-----------------------------------------------------------|
|2021-11-10 11:00:00.000                                    |

Snowflake

OUT -> Oracle_04.sql

SELECT AsDate + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM
TIMES
) FROM
TIMES;

SELECT AsTimestamp + (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM
TIMES
) FROM
TIMES;

|PUBLIC.DATEADD_UDF( ASDATE, (SELECT EXTRACT(DAY FROM ASTIMESTAMPTWO) FROM PUBLIC.TIMES))|
|----------------------------------------------------------------------------------------|
|2021-11-11                                                                              |


|PUBLIC.DATEADD_UDF( ASTIMESTAMP, (SELECT EXTRACT(DAY FROM ASTIMESTAMPTWO) FROM PUBLIC.TIMES))|
|---------------------------------------------------------------------------------------------|
|2021-11-10 11:00:00.000                                                                      |

Subtraction

Combination Matrix

Subtraction

Date

Timestamp

Number

Interval

Unknown

Float

Date

DATEDIFF

TIMESTAMP_DIFF_UDF

Date - Interval day

Date - Interval IntervalUnit

DATEDIFF_UDF

Timestamp

TIMESTAMP_DIFF_UDF

Timestamp - Interval day

Timestamp - Interval IntervalUnit

DATEDIFF_UDF

Number

INVALID

Number - Number

INVALID

MSCEWI3036

Number - Float

Interval

INVALID

MSCEWI3116

Unknown - Interval IntervalUnit

NOT SUPPORTED IN ORACLE

Unknown

DATEDIFF_UDF

MSCEWI3036

Unknown - Interval IntervalUnit

MSCEWI3036

Float

DATEDIFF_UDF

Float - Number

NOT SUPPORTED IN ORACLE

MSCEWI3036

Float - Float

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

Invalid

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

Oracle

SELECT 1 - AsDate FROM TIMES;

SELECT 1 - AsTimestamp FROM TIMES;

SQL Error [932] [42000]: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

SQL Error [932] [42000]: ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

DATEDIFF

The subtraction between two operands of date type is converted to the Snowflake DATEDIFF function, using as a time unit (first parameter) ‘day’. For example

DATEDIFF — Snowflake Documentation

Oracle

IN -> Oracle_05.sql

SELECT AsDate - AsDateTwo FROM TIMES;

|ASDATE-ASDATETWO|
|----------------|
|1               |

Snowflake

OUT -> Oracle_05.sql

SELECT AsDate - AsDateTwo FROM
TIMES;

|DATEDIFF(DAY, ASDATETWO, ASDATE)|
|--------------------------------|
|1                               |

Date - Interval day

This is the current transformation for the subtraction operation between a date type and a number. For example

Oracle

IN -> Oracle_06.sql

SELECT AsDate - 1 FROM TIMES;

SELECT AsDate + -1 FROM TIMES;

|ASDATE-1               |
|-----------------------|
|2021-11-05 00:00:00.000|

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

Snowflake

OUT -> Oracle_06.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 unknown AND Number ***/!!! AsDate + -1 FROM
 TIMES;

|ASDATE - INTERVAL '1 DAY'|
|-------------------------|
|2021-11-05               |

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

Timestamp - Interval day

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

Oracle

IN -> Oracle_07.sql

SELECT AsTimestamp - 1 FROM TIMES;

SELECT AsTimestamp + -1 FROM TIMES;

|ASTIMESTAMP-1          |
|-----------------------|
|2021-11-04 11:00:00.000|

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

Snowflake

OUT -> Oracle_07.sql

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

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

|ASTIMESTAMP - INTERVAL '1 DAY'|
|------------------------------|
|2021-11-04 11:00:00.000       |

|ASTIMESTAMP + INTERVAL '-1 DAY'|
|-------------------------------|
|2021-11-04 11:00:00.000        |

Note: In Oracle, both DATE and TIMESTAMP columns contain a time component, but Oracle uses 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 the TIMESTAMP and Intervals, the result could be shown as DATE, hiding the time component, unless the NLS_DATE_FORMAT parameter is changed.

More information about the NLS_DATE_FORMAT format can be found here.

TIMESTAMP_DIFF_UDF

The subtractions between timestamp types and dates with a timestamp and vice versa; are resolved by inserting the TIMESTAMP_DIFF_UDF user-defined function, (implementation here). For example

Oracle

IN -> Oracle_08.sql

SELECT AsTimeStamp - AsTimeStampTwo FROM TIMES;

SELECT AsTimeStamp - AsDateTwo FROM TIMES;

SELECT AsDateTwo - AsTimeStamp FROM TIMES;

|ASTIMESTAMP-ASTIMESTAMPTWO|
|--------------------------|
|+000000000 01:00:00.000000|

|ASTIMESTAMP-ASDATETWO     |
|--------------------------|
|+000000000 11:00:00.000000|

|ASDATETWO-ASTIMESTAMP     |
|--------------------------|
|-000000000 11:00:00.000000|

Snowflake

OUT -> Oracle_08.sql

SELECT AsTimeStamp - AsTimeStampTwo FROM
TIMES;

SELECT AsTimeStamp - AsDateTwo FROM
TIMES;

SELECT AsDateTwo - AsTimeStamp FROM
TIMES;

|PUBLIC.TIMESTAMP_DIFF_UDF( ASTIMESTAMP, ASTIMESTAMPTWO)|
|-------------------------------------------------------|
|+000000000  01:00:00.00000000                          |


|PUBLIC.TIMESTAMP_DIFF_UDF( ASTIMESTAMP, ASDATETWO)|
|--------------------------------------------------|
|+000000000  11:00:00.00000000                     |


|PUBLIC.TIMESTAMP_DIFF_UDF( ASDATETWO, ASTIMESTAMP)|
|--------------------------------------------------|
|-000000000 -11:00:00.00000000                     |

DATEDIFF_UDF

For those cases where there is an addition operation between a date or timestamp type and an unknown type, a user-defined function (UDF) is added. The UDF implementation can be found here, but it could be edited to perform what is required. The UDF is located in the UDFs folder. For example:

Oracle

IN -> Oracle_09.sql

SELECT ASDATE - (EXTRACT(DAY FROM ASDATE)) FROM TIMES;

SELECT ASTIMESTAMP - (EXTRACT(DAY FROM ASDATE)) FROM TIMES;

|ASDATE-(EXTRACT(DAYFROMASDATE))|
|-------------------------------|
|2021-10-31 00:00:00.000        |

|ASTIMESTAMP-(EXTRACT(DAYFROMASDATE))|
|------------------------------------|
|2021-10-30 11:00:00.000             |

Snowflake

OUT -> Oracle_09.sql

SELECT ASDATE - (EXTRACT(DAY FROM ASDATE)) FROM
TIMES;


SELECT ASTIMESTAMP - (EXTRACT(DAY FROM ASDATE)) FROM
TIMES;

|PUBLIC.DATEDIFF_UDF( ASDATE, (EXTRACT(DAY FROM ASDATE)))|
|--------------------------------------------------------|
|2021-10-31                                              |

|PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, (EXTRACT(DAY FROM ASDATE)))|
|-------------------------------------------------------------|
|2021-10-30 11:00:00.000                                      |

Common Cases

Warning: MSCEWI3036

This warning is used to indicate whether an addition or subtraction operation may not behave correctly due to the operands data types. It means that maybe the result of the operation in Snowflake is not functionally equivalent to Oracle. The addition and subtraction between a date or numeric type and an unknown type are one of the most common cases. For example

Input Code

IN -> Oracle_10.sql

SELECT AsDate - (EXTRACT(DAY FROM ASDATE)) FROM TIMES;

Output Code

OUT -> Oracle_10.sql

SELECT AsDate - (EXTRACT(DAY FROM ASDATE)) FROM
TIMES;

This EWI is added in operations where the type of a column could not be resolved, if the column type is INTERVAL and it is operated only with other intervals, EWI will be added but code will not be commented out. The following example describes this behavior:

Input Code

IN -> Oracle_11.sql

SELECT INTERVAL '1' DAY + interval_column FROM UNKNOWN_TABLE;

Output Code

OUT -> Oracle_11.sql

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
interval_column + INTERVAL '1 day' FROM
UNKNOWN_TABLE;

Known Issues

1. TIMESTAMP DIFF UDF improvement

The TIMESTAMP_DIFF_UDF must be improved to be able to specify the return type. It means adding a third parameter where it is possible to specify the time part, for example, day, hour, month, etc.

2. Built-in functions as operators

There is currently no management for date operations between built-in functions that return date types.

3. Multiple operands

Currently, there is no management for date operation with more than two operands, it may work but you may also find issues.

4. Comparison operators

Currently, there is no management for date operations with comparison operators like greater than, lower than, etc.

5. Output format

The result's format of the arithmetic operations could be changed by using the next command ALTER SESSION SET DATE_OUTPUT_FORMAT = 'DESIRED-FORMAT'; in Snowflake.

6. Issues in interval operations with seconds precision

Some operations may differ in precision, specifically those that include intervals with seconds precision, this is because Oracle rounds depending on the precision, Snowflake's interval does not support seconds with decimal places, in order to have the same result, it is necessary to change the second decimal places by milliseconds in intervals considering the rounding that Oracle performs. The following example shows this issue

Oracle

IN -> Oracle_12.sql

SELECT AsTimeStamp+INTERVAL '15.6789' SECOND(2,3) FROM times;

SELECT AsTimeStamp+INTERVAL '15.6783' SECOND(2,3) FROM times;

|ASTIMESTAMP+INTERVAL'15.6789'SECOND(2,3)|
|----------------------------------------|
|2021-11-05 11:00:15.679                 |

|ASTIMESTAMP+INTERVAL'15.6783'SECOND(2,3)|
|----------------------------------------|
|2021-11-05 11:00:15.678                 |

Snowflake

OUT -> Oracle_12.sql

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 AsTimeStamp + INTERVAL '15.6789 second'
FROM
 times;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!! AsTimeStamp + INTERVAL '15.6783 second'
FROM
 times;

|ASTIMESTAMP + INTERVAL '15.6789 SECOND'|
|---------------------------------------|
|2021-11-05 11:00:16.000                |

|ASTIMESTAMP + INTERVAL '15.6783 SECOND'|
|---------------------------------------|
|2021-11-05 11:00:16.000                |


|ASTIMESTAMP + INTERVAL '15 SECOND, 679 MILLISECOND'|
|---------------------------------------------------|
|2021-11-05 11:00:15.679                            |

|ASTIMESTAMP + INTERVAL '15 SECOND, 678 MILLISECOND'|
|---------------------------------------------------|
|2021-11-05 11:00:15.678                            |

SSC-EWI-0108: The following subquery matches at least one of the patterns considered invalid and may produce compilation errors.
SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.

PreviousINTERVAL DAY TO SECOND Data Type NextInterval UDFs vs Snowflake native interval operation

Last updated 6 months ago

Description

Sample Source Patterns

Addition

Combination Matrix

Invalid

Oracle

Date + Interval day

Oracle

Snowflake

Timestamp + Interval day

Oracle

Snowflake

DATEADD_UDF

Oracle

Snowflake

Subtraction

Combination Matrix

Invalid

Oracle

DATEDIFF

Oracle

Snowflake

Date - Interval day

Oracle

Snowflake

Timestamp - Interval day

Oracle

Snowflake

TIMESTAMP_DIFF_UDF

Oracle

Snowflake

DATEDIFF_UDF

Oracle

Snowflake

Common Cases

Warning: MSCEWI3036

Input Code

Output Code

Input Code

Output Code

Known Issues

1. TIMESTAMP DIFF UDF improvement

2. Built-in functions as operators

3. Multiple operands

4. Comparison operators

5. Output format

6. Issues in interval operations with seconds precision

Oracle

Snowflake

Related EWIs