Date & Time Data Types

This topic describes the date, time, and timestamp (combined date + time) data types supported in Snowflake along with the supported formats for string constants/literals.

In this Topic:

Data Types

DATE

Snowflake supports a single DATE data type for storing dates (without time). DATE accepts dates in the most common forms such as YYYY-MM-DD, DD-MON-YYYY, etc. All accepted timestamps are valid inputs for dates as well.

DATETIME

DATETIME is an alias for TIMESTAMP_NTZ.

TIME

Snowflake supports a single TIME data type for storing times in the form of HH:MI:SS. TIME supports an optional precision parameter for fractional seconds, e.g. TIME(3). Time precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.

All TIME values must be between 00:00:00 and 23:59:59.999999999. TIME internally stores “wallclock” time, and all operations on it are performed without taking any time zone into consideration.

TIMESTAMP

TIMESTAMP in Snowflake is a user-specified alias to one of the specific TIMESTAMP_* variations. In all operations where TIMESTAMP is used, the specified TIMESTAMP_* variation is automatically used. The TIMESTAMP data type is never stored in tables.

The TIMESTAMP variation used for the TIMESTAMP data type is specified by the TIMESTAMP_TYPE_MAPPING session parameter (default is TIMESTAMP_NTZ).

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

Snowflake supports three variations of timestamp. All timestamp variations, including the TIMESTAMP alias, supports an optional precision parameter for fractional seconds, e.g. TIMESTAMP(3). Timestamp precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.

TIMESTAMP_LTZ

TIMESTAMP_LTZ internally stores UTC time with a specified precision. However, all operations are performed in the current session’s time zone, controlled by the TIMEZONE session parameter.

The following data types are aliases for TIMESTAMP_LTZ:

  • TIMESTAMPLTZ
  • TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP_NTZ

TIMESTAMP_NTZ internally stores “wallclock” time with a specified precision. All operations are performed without taking any time zone into account.

If the output format contains a time zone, the UTC time zone indicator (Z) is displayed.

The following data types are aliases for TIMESTAMP_NTZ:

  • TIMESTAMPNTZ
  • TIMESTAMP WITHOUT TIME ZONE

Also, by default, the TIMESTAMP alias uses TIMESTAMP_NTZ.

TIMESTAMP_TZ

TIMESTAMP_TZ internally stores UTC time together with an associated time zone offset. When a time zone is not provided, the session time zone offset is used. All operations are performed with the time zone offset specific to each record.

The following data types are aliases for TIMESTAMP_TZ:

  • TIMESTAMPTZ
  • TIMESTAMP WITH TIME ZONE

Attention

TIMESTAMP_TZ currently only stores the offset of a given time zone at the moment of creation for a given value, not the actual time zone.

For example, with the TIMEZONE parameter set to "America/Los_Angeles", converting a value to TIMESTAMP_TZ in January of a given year stores the time zone offset of -0800. If 6 months are later added to the value, the -0800 offset is retained, even though in July the offset for Los Angeles is -0700. This is because after the value is created, the actual time zone information ("America/Los_Angeles") is no longer available.

SELECT '2017-01-01 12:00:00'::TIMESTAMP_TZ;

-------------------------------------+
 '2017-01-01 12:00:00'::TIMESTAMP_TZ |
-------------------------------------+
 2017-01-01 12:00:00 -0800           |
-------------------------------------+

SELECT DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ);

--------------------------------------------------------+
 DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ) |
--------------------------------------------------------+
 2017-07-01 12:00:00 -0800                              |
--------------------------------------------------------+

Date and Time Formats

All of these data types accept most reasonable non-ambiguous date, time, or date + time formats. Manually-specified date and time formats can include elements (case-insensitive) from the following list:

Format Description
YYYY Four-digit year.
YY Two-digit year, controlled by the TWO_DIGIT_CENTURY_START session parameter, e.g. when set to 1980, values of 79 and 80 parsed as 2079 and 1980 respectively.
MM Two-digit month (01=January, etc.).
MON Abbreviated month name.
DD Two-digit day of month (01 through 31).
DY Abbreviated day of week.
HH24 Two digits for hour (00 through 23); am/pm NOT allowed.
HH12 Two digits for hour (01 through 12); am/pm allowed.
AM , PM Ante meridiem (am) / post meridiem (pm); for use with HH12.
MI Two digits for minute (00 through 59).
SS Two digits for second (00 through 59).
FF Fractional seconds with precision 0 (seconds) to 9 (nanoseconds), e.g. FF, FF0, FF3, FF9. Specifying FF is equivalent to FF6 (microseconds).
TZH:TZM , TZHTZM , TZH Time zone hour and minute, offset from UTC. Can be prefixed by +/- for sign.

Note

When a date-only format is used, the associated time is assumed to be midnight on that day.

Date & Time Constants

Constants (also known as literals) refers to fixed data values. Snowflake supports specifying fixed date, time, or timestamp values using string constants. Constants must always be enclosed between delimiter characters. Snowflake supports using single quotes to delimit string constants. For example:

date '2010-09-14'
time '10:03:56'
timestamp '2009-09-15 10:59:43'

The string is parsed as a date, time, or timestamp value based on the input parameter for the data type:

Date:DATE_INPUT_FORMAT
Time:TIME_INPUT_FORMAT
Timestamp:TIMESTAMP_INPUT_FORMAT

Examples

Insert a specific date into the date column in a table:

CREATE TABLE t (d1 DATE);
INSERT INTO t (d1) VALUES (DATE '2011-10-29');

Interval Constants

Use interval constants to add or subtract a period of time from a date, time, or timestamp.

Interval constants are implemented using the INTERVAL keyword, which has the following syntax:

INTERVAL <integer> [ <date_time_part> ] [ , [ <integer> <date_time_part> ] ... ]

The INTERVAL keyword supports integers and optional date or time parts. For example INTERVAL '1 YEAR' represents 1 year. If a date or time part is not provided, the interval represents seconds, e.g. INTERVAL '2' is the same as INTERVAL '2 seconds'. Snowflake supports using single quotes to delimit string constants.

Interval constants can include multiple date and time parts, e.g. INTERVAL '4 year, 5 months, 3 hours' represents 4 years, 5 months, and 3 hours.

For the list of supported date and time parts, see Supported Date and Time Parts in this topic.

Note

  • The order of interval increments is important. The increments are added or subtracted in the order listed, e.g. INTERVAL '1 year, 1 day' first adds a year and then a day, but INTERVAL '1 day, 1 year' first adds a day and then a year. This can affect calculations influenced by calendar events such as leap years:

    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 day, 1 year';
    
    +---------------------------------------------------+
    | TO_DATE ('2019-02-28') + INTERVAL '1 DAY, 1 YEAR' |
    |---------------------------------------------------|
    | 2020-03-01                                        |
    +---------------------------------------------------+
    
    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 year, 1 day';
    
    +---------------------------------------------------+
    | TO_DATE ('2019-02-28') + INTERVAL '1 YEAR, 1 DAY' |
    |---------------------------------------------------|
    | 2020-02-29                                        |
    +---------------------------------------------------+
    
  • Interval constants are not supported in column definitions.

Supported Date and Time Parts

The INTERVAL keyword accepts the following date and time parts as an argument:

Date or Time Parts Abbreviations / Variations
year y , yy , yyy , yyyy , yr , years , yrs
quarter q , qtr , qtrs , quarters
month mm , mon , mons , months
week [1] w , wk , weekofyear , woy , wy , weeks
day d , dd , days, dayofmonth
hour h , hh , hr , hours , hrs
minute m , mi , min , minutes , mins
second s , sec , seconds , secs
millisecond ms , msec , milliseconds
microsecond us , usec , microseconds
nanosecond ns , nsec , nanosec , nsecond , nanoseconds , nanosecs , nseconds

Examples

Add a year to a specific date:

select to_date('2018-04-15') + INTERVAL '1 year';

+-------------------------------------------+
| TO_DATE('2018-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2019-04-15                                |
+-------------------------------------------+

Add 3 hours and 18 minutes to a specific time:

select to_time('04:15:29') + INTERVAL '3 hours, 18 minutes';

+------------------------------------------------------+
| TO_TIME('04:15:29') + INTERVAL '3 HOURS, 18 MINUTES' |
|------------------------------------------------------|
| 07:33:29                                             |
+------------------------------------------------------+

Add a complex interval to the output of the CURRENT_TIMESTAMP function:

select current_timestamp + INTERVAL '1 year, 3 quarters, 4 months, 5 weeks, 6 days, 7 minutes, 8 seconds, 1000 milliseconds, 4000000 microseconds, 5000000001 nanoseconds' as complex_interval1;

+-------------------------------+
| COMPLEX_INTERVAL              |
|-------------------------------|
| 2020-12-28 08:08:01.325 -0800 |
+-------------------------------+

Add a complex interval with abbreviated date/time part notation to a specific date:

select to_date('2025-01-17') + INTERVAL '1 y, 3 q, 4 mm, 5 w, 6 d, 7 h, 9 m, 8 s, 1000 ms, 445343232 us, 898498273498 ns' as complex_interval2;

+-------------------------+
| COMPLEX_INTERVAL2       |
|-------------------------|
| 2027-03-30 07:31:32.841 |
+-------------------------+

Query a table of employee information and return the names of employees who were hired within the past 2 years and 3 months:

select name, hire_date from employees where hire_date > current_date - INTERVAL '2 y, 3 month';

Filter a timestamp column and add 4 seconds to each returned value:

select ts + INTERVAL '4 seconds' from intervals where ts > to_timestamp('2014-04-05 01:02:03');

Simple Date Arithmetic

Date type values support basic addition (+) and subtraction (-) calculations using integers, which represent n days.

Note that time and timestamp values do not yet support simple arithmetic.

Examples

Add 1 day to a specific date:

select to_date('2018-04-15') + 1;

+---------------------------+
| TO_DATE('2018-04-15') + 1 |
|---------------------------|
| 2018-04-16                |
+---------------------------+

Subtract 4 days from a specific date:

select to_date('2018-04-15') - 4;

+---------------------------+
| TO_DATE('2018-04-15') - 4 |
|---------------------------|
| 2018-04-11                |
+---------------------------+

Query a table of employee information and return the names of people who left the company but were employeed more than 365 days:

select name from employees where end_date > start_date + 365;

General Examples

For additional date and timestamp usage examples, see Using Dates and Timestamps.

Timestamp Examples

Create table using different timestamps:

-- First, use TIMESTAMP (mapped to TIMESTAMP_NTZ)

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP);

DESC TABLE ts_test;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

-- Next, explicitly use one of the TIMESTAMP variations (TIMESTAMP_LTZ)

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);

DESC TABLE ts_test;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_LTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Use TIMESTAMP_LTZ with different time zones:

CREATE OR REPLACE TABLE ts_test(ts timestamp_ltz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Notice how the time for January 2nd is 08:00 in Los Angeles (which is 16:00 in UTC)

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 08:00:00 -0800 |        8 |
+---------------------------------+----------+

-- Next, notice how the times change with a different time zone

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 19:00:00 -0500 |       19 |
| Thu, 02 Jan 2014 11:00:00 -0500 |       11 |
+---------------------------------+----------+

Use TIMESTAMP_NTZ:

CREATE OR REPLACE TABLE ts_test(ts timestamp_ntz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Notice how both times from different time zones are converted to the same "wallclock" time

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

-- Next, notice how a session time zone change does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

Use TIMESTAMP_TZ:

CREATE OR REPLACE TABLE ts_test(ts timestamp_tz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Notice how the January 1st record inherited the session time zone,
-- and how "America/Los_Angeles" was converted into a numeric time zone offset

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+

-- Next, notice how the session time zone change does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+