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.

CREATE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

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

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

INSERT INTO PUBLIC.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

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

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

SELECT AsDate + INTERVAL '1 DAY' FROM TIMES;

SELECT AsDate + INTERVAL '1 DAY' 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

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

SELECT AsTimestamp + INTERVAL '1 DAY' FROM PUBLIC.TIMES;

SELECT AsTimestamp + INTERVAL '1 DAY' FROM PUBLIC.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

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

SELECT
/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown ***/
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATEADD_UDF' INSERTED. ***/
PUBLIC.DATEADD_UDF( AsDate, (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM PUBLIC.TIMES))
FROM PUBLIC.TIMES;

SELECT
/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATEADD_UDF' INSERTED. ***/
PUBLIC.DATEADD_UDF( AsTimestamp, (SELECT EXTRACT(DAY FROM AsTimestampTwo) FROM PUBLIC.TIMES))
FROM PUBLIC.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	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

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

Oracle

SELECT AsDate - AsDateTwo FROM TIMES;

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

Snowflake

SELECT
DATEDIFF(day, AsDateTwo, AsDate)
FROM PUBLIC.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

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

SELECT AsDate - INTERVAL '1 DAY' FROM PUBLIC.TIMES;

SELECT AsDate + INTERVAL '-1 DAY' FROM PUBLIC.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

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

SELECT AsTimestamp - INTERVAL '1 DAY' FROM PUBLIC.TIMES;

SELECT AsTimestamp + INTERVAL '-1 DAY' FROM PUBLIC.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

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

SELECT
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TIMESTAMP_DIFF_UDbe F' INSERTED. ***/
PUBLIC.TIMESTAMP_DIFF_UDF( AsTimeStamp, AsTimeStampTwo)
from PUBLIC.TIMES;
  
SELECT
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TIMESTAMP_DIFF_UDF' INSERTED. ***/
PUBLIC.TIMESTAMP_DIFF_UDF( AsTimeStamp, AsDateTwo)
from PUBLIC.TIMES;

SELECT
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TIMESTAMP_DIFF_UDF' INSERTED. ***/
PUBLIC.TIMESTAMP_DIFF_UDF( AsDateTwo, AsTimeStamp)
from PUBLIC.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

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

SELECT
/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown ***/
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATEDIFF_UDF' INSERTED. ***/
PUBLIC.DATEDIFF_UDF( ASDATE, (EXTRACT(DAY FROM ASDATE)))
FROM PUBLIC.TIMES;

SELECT
/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATEDIFF_UDF' INSERTED. ***/
PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, (EXTRACT(DAY ASDATE)))
FROM PUBLIC.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

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

Output Code

SELECT
/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown ***/
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATEDIFF_UDF' INSERTED. ***/
PUBLIC.DATEDIFF_UDF( AsDate, (EXTRACT(DAY FROM ASDATE)))
FROM PUBLIC.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

SELECT INTERVAL '1' DAY + interval_column FROM UNKNOWN_TABLE;

Output Code

SELECT
/*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/
( interval_column) + INTERVAL '1 day'
FROM PUBLIC.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

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

--Default Snow Convert transformation
SELECT AsTimeStamp + INTERVAL '15.6789 second' FROM PUBLIC.times;

SELECT AsTimeStamp + INTERVAL '15.6783 second' FROM PUBLIC.times;

--Conversion with the fix
SELECT AsTimeStamp + INTERVAL '15 second, 679 millisecond' FROM PUBLIC.times;

SELECT AsTimeStamp + INTERVAL '15 second, 678 millisecond' FROM PUBLIC.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                            |

: Custom UDF inserted
: Arithmetic operation types not supported
MSCEWI3116: Operations between Intervals are not supported

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

Last updated 1 year ago

Description

Sample Source Patterns

Consider the next table for the examples below.

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.