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.

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 TIMESTAMP values are valid inputs for dates; however, the TIME information is truncated.

For DATE and TIMESTAMP data, Snowflake recommends using years between 1582 and 9999. Snowflake accepts some years outside this range, but years prior to 1582 should be avoided due to limitations on the Gregorian Calendar.

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.

For DATE and TIMESTAMP data, Snowflake recommends using years between 1582 and 9999. Snowflake accepts some years outside this range, but years prior to 1582 should be avoided due to limitations on the Gregorian Calendar.

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_NTZ , TIMESTAMP_TZ

Snowflake supports three variations of timestamp.

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

TIMESTAMP_TZ values are compared based on their times in UTC. For example, the following comparison between different times in different timezones returns TRUE because the two values have equivalent times in UTC.

select '2021-01-01 00:00:00 +0000'::timestamp_tz = '2021-01-01 01:00:00 +0100'::timestamp_tz;
Copy

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

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

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

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

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

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. The UUUU format element supports negative years.

Date and Time Formats

All of these data types accept most reasonable non-ambiguous date, time, or date + time formats. See Supported Formats for AUTO Detection for the formats that Snowflake recognizes when configured to detect the format automatically.

You can also specify the date and time format manually. When specifying the format, you can use the case-insensitive elements listed in the following table:

Format Element

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

Full or abbreviated month name.

MMMM

Full month name.

DD

Two-digit day of month (01 through 31).

DY

Abbreviated day of week.

HH24

Two digits for hour (00 through 23). You must not specify AM / PM.

HH12

Two digits for hour (01 through 12). You can specify AM / PM.

AM , PM

Ante meridiem (am) / post meridiem (pm). Use this only with HH12 (not with HH24).

MI

Two digits for minute (00 through 59).

SS

Two digits for second (00 through 59).

FF[0-9]

Fractional seconds with precision 0 (seconds) to 9 (nanoseconds), e.g. FF, FF0, FF3, FF9. Specifying FF is equivalent to FF9 (nanoseconds).

TZH:TZM , TZHTZM , TZH

Time zone hour and minute, offset from UTC. Can be prefixed by +/- for sign.

UUUU

Four-digit year in ISO format, which are negative for BCE years.

Note

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

  • Anything in the format between double quotes or other than the above elements is parsed/formatted without being interpreted.

  • For more details about valid ranges, number of digits, and best practices, see Additional Information About Using Date, Time, and Timestamp Formats.

Examples of Using Date and Time Formats

The following example uses “FF” to indicate that the output should have 9 digits in the fractional seconds field:

CREATE TABLE timestamp_demo_table(t  TIMESTAMP,
                                  t_tz TIMESTAMP_TZ,
                                  t_ntz TIMESTAMP_NTZ,
                                  t_ltz TIMESTAMP_LTZ);
INSERT INTO timestamp_demo_table (t, t_tz, t_ntz, t_ltz) VALUES (
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789'
    );
Copy
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Copy
SELECT t, t_tz, t_ntz, t_ltz 
    FROM timestamp_demo_table;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| T                             | T_TZ                          | T_NTZ                         | T_LTZ                         |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
Copy

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'
Copy

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

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> ] ... ]'
Copy

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

The INTERVAL keyword supports one 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                                        |
    +---------------------------------------------------+
    
    Copy
  • 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                                |
+-------------------------------------------+
Copy

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

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

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

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

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

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

Subtract 4 days from a specific date:

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

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

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;
Copy