Date & Time Data Types

This topic describes the date, time, and timestamp (combined date + time) data types supported in Snowflake.

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.

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