INTERVAL
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)
There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR.
Sample Source Patterns
Interval with a single DateTime part
BigQuery
Snowflake
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
BigQuery
Snowflake
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
BigQuery
Snowflake
In BigQuery the datetime_part follows the next canonical format
Interval comparison
BigQuery
Snowflake
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.
Snowflake
Related EWIs
MSCEWI1036: Data type converted to another data type.
MSCEWI1107: Interval Literal Not Supported In Current Scenario.
Last updated