Date & Time Data Types

This topic describes the data types supported in Snowflake for managing dates, times, and timestamps (combined date + time). It also describes the supported formats for string constants used in manipulating dates, times, and timestamps.

In this Topic:

Data Types

DATE

Snowflake supports a single DATE data type for storing dates (with no time elements). DATE accepts dates in the most common forms (YYYY-MM-DD, DD-MON-YYYY, etc.). In addition, all accepted timestamps are valid inputs for dates.

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 TIME values are performed without taking any time zone into consideration.

TIMESTAMP

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

The TIMESTAMP_* variation associated with TIMESTAMP is specified by the TIMESTAMP_TYPE_MAPPING session parameter. The default is TIMESTAMP_NTZ.

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

Snowflake supports three variations of timestamp. All timestamp variations, as well as the TIMESTAMP alias, support 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.

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 indicator (Z) is displayed.

TIMESTAMP_NTZ is the default for TIMESTAMP.

Aliases for TIMESTAMP_NTZ:

  • TIMESTAMPNTZ

  • TIMESTAMP WITHOUT TIME ZONE

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.

Aliases for TIMESTAMP_TZ:

  • TIMESTAMPTZ

  • TIMESTAMP WITH TIME ZONE

Attention

TIMESTAMP_TZ currently only stores the offset of a given time zone, not the actual time zone, at the moment of creation for a given value. This is especially important to note when dealing with daylight saving time, which is not utilized by UTC.

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. The following code sample illustrates this behavior:

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                              |
--------------------------------------------------------+

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

-- Note that 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, note that 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');

-- Note that 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, note that changing the session time zone 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');

-- Note that the January 1st record inherited the session time zone,
-- and "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, note that changing the session time zone 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 |
+---------------------------------+----------+

Supported Calendar

Snowflake uses the Gregorian Calendar for all dates and timestamps. The Gregorian Calendar starts in the year 1582, but recognizes prior years, which is important to note because Snowflake does not adjust dates prior to 1582 (or calculations involving dates prior to 1582) to match the Julian Calendar.

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 the following elements (case-insensitive):

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 and Time Constants

Constants (also known as literals) refers to fixed data values. Snowflake supports using string constants to specify fixed date, time, or timestamp values. String 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 format for the data type, as set through the following parameters:

DATE

DATE_INPUT_FORMAT

TIME

TIME_INPUT_FORMAT

TIMESTAMP

TIMESTAMP_INPUT_FORMAT

For example, to insert a specific date into a column in a table:

CREATE TABLE t1 (d1 DATE);

INSERT INTO t1 (d1) VALUES (DATE '2011-10-29');

Interval Constants

You can use interval constants to add or subtract a period of time to/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> ] ... ]'

As with all string constants, Snowflake requires single quotes to delimit interval constants.

The INTERVAL keyword supports one more more integers and, optionally, one or more date or time parts. For example:

  • INTERVAL '1 YEAR' represents 1 year.

  • INTERVAL '4 years, 5 months, 3 hours' represents 4 years, 5 months, and 3 hours.

If a date or time part is not specified, the interval represents seconds (e.g. INTERVAL '2' is the same as INTERVAL '2 seconds'). Note that this is different from the default unit of time for performing date arithmetic. For more details, see Simple Arithmetic for Dates (in this topic).

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

Note

  • The order of interval increments is important. The increments are added or subtracted in the order listed. For example:

    • INTERVAL '1 year, 1 day' first adds/subtracts a year and then a day.

    • INTERVAL '1 day, 1 year' first adds/subtracts 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 is not a data type (i.e. you cannot define a table column to be of data type INTERVAL). Intervals can only be used in date, time, and timestamp arithmetic.

Supported Date and Time Parts for Intervals

The INTERVAL keyword supports the following date and time parts as arguments (case-insensitive):

Date or Time Part

Abbreviations / Variations

year

y , yy , yyy , yyyy , yr , years , yrs

quarter

q , qtr , qtrs , quarters

month

mm , mon , mons , months

week

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

Interval Examples

Add a year interval 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 a 3 hour and 18 minute interval 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 named ts from a table named tl and add 4 seconds to each returned value:

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

Simple Arithmetic for Dates

In addition to using interval constants to add to and subtract from dates, time, and timestamps, Snowflake also supports the basic addition and subtraction of days to DATE values, in the form of { + | - } <integer>, where <integer> specifies the number of days to add/subtract.

Note

TIME and TIMESTAMP values do not yet support simple arithmetic.

Date 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 named employees and return the names of people who left the company, but were employed more than 365 days:

select name from employees where end_date > start_date + 365;