Data types supported in Snowflake for managing Dates

Snowflake

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.

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.

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.

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.

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

TIMESTAMP_NTZ is the default for TIMESTAMP.
Aliases for 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.

Author: user

Leave a Reply