INTERVAL
Interval data type and usages
Description
An
INTERVAL
object represents duration or amount of time, without referring to any specific point in time. There is no equivalent in Snowflake so it is transformed to Varchar (BigQuery Language Reference INTERVAL Data Type)
Syntax
Sample Source Patterns
Interval with a single DateTime part
Input Code:
Output Code:
Snowflake does not support the scenario where the Interval data type is queried directly, on the contrary when it is used as an operator for a given date its translation is done using an Interval constant (if possible).
Interval with a DateTime part range
Input Code:
Output Code:
The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. In order to use the value, it needs to be extracted and used as an Interval constant (if possible).
Interval as a Column data type
Input Code:
Output Code:
In BigQuery the datetime_part follows the next canonical format:
Interval comparison
Input Code:
Output Code:
As is known, Snowflake only supports Interval as a data type in arithmetic operations, which is why the CURRENT_TIMESTAMP
function is added to each operand to correctly support the comparison.
Known Issues
1. Only arithmetic operations are supported
Snowflake Intervals have several limitations. Only arithmetic operations between DATE
or TIMESTAMP
and Interval Constants are supported, every other scenario is not supported. For more information please review MSCEWI1107.
2. Working with signs in the Interval data type
In BigQuery, when the substring corresponding to the year-month is preceded by a sign (+ -), it affects both the year and the month. In a similar way, it works for the substring corresponding to the time, in this case, the following affects the hour, minute, and second. An example of this is shown below.
Input:
Output:
Related EWIs
SSC-EWI-0036: Data type converted to another data type.
SSC-EWI-0107: Interval Literal Not Supported In Current Scenario.
Last updated