Snowflake Interval Data Types and Conversion Examples

The INTERVAL data types in Snowflake are handy and widely used types when working with date, time variables. Snowflake supports interval types in the same way as other PostgreSQL databases such as Netezza, Redshift, Vertica, Oracle, etc. In this article, we will check interval types with some common use cases.

Snowflake Interval Data Types

You can express interval types as a combination of the INTERVAL keyword with a numeric quantity and a supported date part; for example: INTERVAL ‘1 days’ or INTERVAL ’10 minutes’.

The Snowflake INTERVAL functions are commonly used to manipulate date and time variables or expressions. For example, you can use interval data type functions to add years, months, days, hours, etc to the timestamp variables. You can use these interval literals in conditions and calculations that involve date-time expressions.

Snowflake Interval Literals (units)

Following are the Snowflake interval literals:

Snowflake Interval Units	Examples along with units
seconds/second/s	INTERVAL ‘5 seconds’
minutes/ minute/m	INTERVAL ’10 minutes’
hours/hour/h	INTERVAL ‘0.5 hour’
days/day/d	INTERVAL ‘1 day’
weeks/week/w	INTERVAL ‘2 weeks’
months/month	INTERVAL ‘1 month’
years/year	INTERVAL ‘1 years’

Similar to Redshift Abbreviations and plurals of each unit are also supported; for example: 10 s, 10 second, and 10 seconds are equivalent intervals. You can use any abbreviations and plurals as per your convenience.

Snowflake Interval Types Implementation

You can implement an INTERVAL constants in a same or slightly different way compared to other databases.

Snowflake supports interval qualifiers such as years, months, weeks, days, etc. Refer previous section for Snowflake supported interval units.
You can specify the quantity value as a fraction. For example, 0.5 hours.
In Snowflake, interval literals must include the units like ‘1 days’. Snowflake will ignore the interval part if unit qualifier is not specified.

Create Snowflake Table with Interval Data Type

Snowflake supports creating table using interval constants. You have to use CREATE TABLE AS (CTAS) to use interval types.

For example, consider following example to create table that uses interval data types.

create temp table test_temp as 
select CURRENT_TIMESTAMP + INTERVAL '2  weeks' as dt;
+---------------------------------------+
| status                                |
|---------------------------------------|
| Table TEST_TEMP successfully created. |
+---------------------------------------+

select * from TEST_TEMP;
+-------------------------------+
| DT                            |
|-------------------------------|
| 2020-01-25 07:25:31.663 -0800 |
+-------------------------------+

Snowflake Interval Date Types Examples

Following are some of commonly used INTERVAL data type conversion examples.

Add a 1 days to the current date time using Snowflake SQL interval types

For example,

select CURRENT_TIMESTAMP;
+-------------------------------+
| CURRENT_TIMESTAMP             |
|-------------------------------|
| 2020-01-11 07:31:36.786 -0800 |
+-------------------------------+

select CURRENT_TIMESTAMP + INTERVAL '1 day';
+--------------------------------------+
| CURRENT_TIMESTAMP + INTERVAL '1 DAY' |
|--------------------------------------|
| 2020-01-12 07:32:20.288 -0800        |
+--------------------------------------+

Add a 12 hours to the current date time using Snowflake interval types

For example,

select CURRENT_TIMESTAMP + INTERVAL '12 hours';
+-----------------------------------------+
| CURRENT_TIMESTAMP + INTERVAL '12 HOURS' |
|-----------------------------------------|
| 2020-01-11 19:34:08.699 -0800           |
+-----------------------------------------+

Add a 30 seconds to the current date time using Snowflake interval types

For example,

select CURRENT_TIMESTAMP + INTERVAL '30 seconds';
+-------------------------------------------+
| CURRENT_TIMESTAMP + INTERVAL '30 SECONDS' |
|-------------------------------------------|
| 2020-01-11 07:35:45.303 -0800             |
+-------------------------------------------+

Add 2 day, 0.5 hour, 59 minutes to the current date time expression using Snowflake SQL interval type functions

For example,

select CURRENT_TIMESTAMP + INTERVAL '2 day, 0.5 hour, 59 m' as dt;
+-------------------------------+
| DT                            |
|-------------------------------|
| 2020-01-13 09:35:48.499 -0800 |
+-------------------------------+