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.

Addition	Date	Timestamp	Number	Interval	Unknown	Float
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	MSCEWI3036	Number + Float
Interval	Date + Interval IntervalUnit	Timestamp + Interval IntervalUnit	INVALID	MSCEWI3116	Unknown + Interval IntervalUnit	INVALID
Unknown	DATEADD_UDF	DATEADD_UDF	Unknown + Number	Unknown + Interval IntervalUnit	MSCEWI3036	MSCEWI3036
Float	DATEADD_UDF	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

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

Query

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;

Result

|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

Query

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;

Result

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

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

Snowflake

Query

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;

Result

|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	DATEDIFF_UDF
Timestamp	TIMESTAMP_DIFF_UDF	TIMESTAMP_DIFF_UDF	Timestamp - Interval day	Timestamp - Interval IntervalUnit	DATEDIFF_UDF	DATEDIFF_UDF
Number	INVALID	INVALID	Number - Number	INVALID	MSCEWI3036	Number - Float
Interval	INVALID	INVALID	INVALID	MSCEWI3116	Unknown - Interval IntervalUnit	NOT SUPPORTED IN ORACLE
Unknown	DATEDIFF_UDF	DATEDIFF_UDF	MSCEWI3036	Unknown - Interval IntervalUnit	MSCEWI3036	MSCEWI3036
Float	DATEDIFF_UDF	DATEDIFF_UDF	Float - Number	NOT SUPPORTED IN ORACLE	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 subtract operations will be explained below:

Invalid

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

Oracle

Query

SELECT 1 - AsDate FROM TIMES;

SELECT 1 - AsTimestamp FROM TIMES;

Result

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

Query

IN -> Oracle_05.sql

SELECT AsDate - AsDateTwo FROM TIMES;

Result

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

Snowflake

Query

OUT -> Oracle_05.sql

SELECT AsDate - AsDateTwo FROM
TIMES;

Result

|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

Query

IN -> Oracle_06.sql

SELECT AsDate - 1 FROM TIMES;

SELECT AsDate + -1 FROM TIMES;

Result

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

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

Snowflake

Query

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;

Result

|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

Query

IN -> Oracle_07.sql

SELECT AsTimestamp - 1 FROM TIMES;

SELECT AsTimestamp + -1 FROM TIMES;

Result

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

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

Snowflake

Query

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;

Result

|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

Query

IN -> Oracle_08.sql

SELECT AsTimeStamp - AsTimeStampTwo FROM TIMES;

SELECT AsTimeStamp - AsDateTwo FROM TIMES;

SELECT AsDateTwo - AsTimeStamp FROM TIMES;

Result

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

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

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

Snowflake

Query

OUT -> Oracle_08.sql

SELECT AsTimeStamp - AsTimeStampTwo FROM
TIMES;

SELECT AsTimeStamp - AsDateTwo FROM
TIMES;

SELECT AsDateTwo - AsTimeStamp FROM
TIMES;

Result

|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

Query

IN -> Oracle_09.sql

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

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

Result

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

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

Snowflake

Query

OUT -> Oracle_09.sql

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


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

Result

|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

Query

IN -> Oracle_12.sql

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

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

Result

|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

Query

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;

Result

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

Related EWIs

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