日期和时间数据类型¶

Snowflake 支持用于管理日期、时间和时间戳（日期 + 时间组合）的数据类型。Snowflake 还支持用于操控日期、时间和时间戳的字符串常量的格式。

数据类型¶

Snowflake 支持以下日期和时间数据类型：

DATE
DATETIME
Interval data types
TIME
TIMESTAMP_LTZ、TIMESTAMP_NTZ、TIMESTAMP_TZ

备注

对于 DATE 和 TIMESTAMP 数据，Snowflake 建议使用 1582 和 9999 之间的年份。Snowflake 可以接受此范围之外的一些年份，但由于公历限制，应避免使用 1582 年之前的年份。

DATE¶

Snowflake 支持存储日期（没有时间元素）的单一 DATE 数据类型。

DATE 可接受最常见的日期形式（YYYY-MM-DD、DD-MON-YYYY 等）。

此外，所有已接受的 TIMESTAMP 值都是日期的有效输入，但是 TIME 信息会被截断。

DATETIME¶

DATETIME is synonymous with TIMESTAMP_NTZ.

Interval data types¶

Interval data types store values that represent a duration of time. You can calculate an interval as the difference between two dates or times. An interval only defines a duration, so it doesn't have a start or end point in time. For example, you might define an interval as three years and seven months.

Snowflake supports the following year-month variations of interval data types:


Data type	描述
INTERVAL YEAR	Represents a duration of time in years.
INTERVAL YEAR TO MONTH	Represents a duration of time in years and months.
INTERVAL MONTH	Represents a duration of time in months.

Snowflake supports the following day-time variations of interval data types:


Data type	描述
INTERVAL DAY	Represents a duration of time in days.
INTERVAL DAY TO HOUR	Represents a duration of time in days and hours.
INTERVAL DAY TO MINUTE	Represents a duration of time in days, hours, and minutes.
INTERVAL DAY TO SECOND	Represents a duration of time in days, hours, minutes, seconds, and fractional seconds.
INTERVAL HOUR	Represents a duration of time in hours.
INTERVAL HOUR TO MINUTE	Represents a duration of time in hours and minutes.
INTERVAL HOUR TO SECOND	Represents a duration of time in hours, minutes, seconds, and fractional seconds.
INTERVAL MINUTE	Represents a duration of time in minutes.
INTERVAL MINUTE TO SECOND	Represents a duration of time in minutes, seconds, and fractional seconds.
INTERVAL SECOND	Represents a duration of time in seconds and fractional seconds.

The following sections describe interval data types in more detail:

Benefits of interval data types
Syntax of interval data types
Representing interval values
Interval formats
Examples of interval values
Operations that involve date and time values
Functions that accept interval values as arguments
Examples for interval data types
Limitations for interval data types

备注

You can also use interval constants for date and time arithmetic. However, interval constants don't support interval storage as a column type.

Benefits of interval data types¶

Interval data types provide the following benefits:

Ensure accurate date arithmetic without ambiguity.
Eliminate the need for manual conversion and casting from integer-based durations.
Optimize storage for data that represents intervals of time.
Optimize query execution for duration data.
Simplify the migration of data from third-party databases, such as Databricks, Oracle, and Teradata.
Comply fully with ANSI standards.

Syntax of interval data types¶

To specify an interval data type, use the following syntax:

INTERVAL { yearMonthQualifier | dayTimeQualifier }

Where:

yearMonthQualifier ::=
  {
    YEAR [ (<precision>) ] [ TO MONTH ]
    | MONTH [ (<precision>) ]
  }

dayTimeQualifier ::=
  {
    DAY [ (<precision>) ] [ TO { HOUR | MINUTE | SECOND [ (<fractional_seconds_precision>) ] } ]
    | HOUR [ (<precision>) ] [ TO { MINUTE | SECOND [ (<fractional_seconds_precision>) ] } ]
    | MINUTE [ (<precision>) ] [ TO SECOND [ (<fractional_seconds_precision>) ] ]
    | SECOND [ (<precision>) [ , (<fractional_seconds_precision>) ] ]
  }

Properties:

precision is the total number of digits that is allowed. Precision can range from 1 to 9.

Default: 9
fractional_seconds_precision is the number of digits in the fractional part of a second. Time precision can range from 0 (seconds) to 9 (nanoseconds).

Default: 9

Use this syntax when you are specifying an interval data type. For example, the following table has a duration column of INTERVAL YEAR TO MONTH type:

CREATE OR REPLACE TEMPORARY TABLE sample_table_with_interval (
  id VARCHAR,
  duration INTERVAL YEAR(2) TO MONTH);

Representing interval values¶

You can represent an interval value by using an interval literal or an interval format:

Interval literals
Interval formats
Examples of interval values

Interval literals¶

An interval literal is an expression that specifies a duration of time in a string literal. Use the following syntax to specify an interval literal:

INTERVAL '[ <sign> ] <string>' { <yearMonthQualifier> | <dayTimeQualifier> }

Where:

sign is an optional symbol that specifies a positive (+) or negative (-) duration of time.

Default: +.
string is a value that represents a time duration.
yearMonthQualifier is a qualifier that is defined in Syntax of interval data types.
dayTimeQualifier is a qualifier that is defined in Syntax of interval data types.

Interval formats¶

String literals in specific formats can represent interval values.

To specify values for years and months, use the following format:

'<sign><Y>-<MM>'

Where:

sign is a required symbol that specifies a positive (+) or negative (-) duration of time.

Default: +.
Y is the number of years. The number of digits that is allowed (precision) depends on the data type of the value.
MM is two digits for the number of months, from 00 to 11.

To specify values for days, hours, seconds, and fractional seconds, use the following format:

'<sign>[<D>] [<HH24>]:[<MI>]:[<SS>].[<F>]'

Where:

sign is a required symbol that specifies a positive (+) or negative (-) duration of time.

Default: +.
D is the number of days. The number of digits that is allowed (precision) depends on the data type of the value.

Omit D for values of the following types:
- INTERVAL HOUR
- INTERVAL HOUR TO MINUTE
- INTERVAL HOUR TO SECOND
- INTERVAL MINUTE
- INTERVAL MINUTE TO SECOND
- INTERVAL SECOND
HH24 is two digits for the number of hours, from 00 to 23.

Omit HH24 for values of the following types:
- INTERVAL DAY
- INTERVAL MINUTE
- INTERVAL MINUTE TO SECOND
- INTERVAL SECOND
MI is two digits for the number of minutes, from 00 through 59.

Omit MI for values of the following types:
- INTERVAL DAY TO HOUR
- INTERVAL DAY
- INTERVAL HOUR
- INTERVAL SECOND
SS is two digits for the number of seconds, from 00 through 59.

Omit SS for values of the following types:
- INTERVAL DAY
- INTERVAL DAY TO HOUR
- INTERVAL DAY TO MINUTE
- INTERVAL HOUR
- INTERVAL HOUR TO MINUTE
- INTERVAL MINUTE
F is the number of fractional seconds for the data types that include seconds. The number of digits that is allowed (precision) depends on the data type of the value.

The following usage notes apply to string literals in interval format:

The string literal representation applies when you use the CAST or TO_CHAR function to cast intervals explicitly to text strings.
Leading zeros in a field specify precision.

Examples of interval values¶

The following table shows how to represent various interval values. The values shown in the table conform to the following rules for interval values:

For positive values, the plus sign + is optional for interval literal values but required for interval format values.
In the interval literal values, the value in parentheses specifies the precision, which is the number of digits that is allowed. For example, YEAR(3) specifies that three digits are allowed in the year.
In the interval format values, the primary field (the leading field) does not include leading zeros. Subordinate fields use a fixed number of digits. For example, in a YEAR TO MONTH value like +1-08, the year field has no leading zeros, and the month field uses two digits.


Duration	Type	Interval literal value	Interval format value
Positive 5 years	INTERVAL YEAR	`INTERVAL '5' YEAR(2)`	`'+5'`
Positive 1 year and 8 months	INTERVAL YEAR TO MONTH	`INTERVAL '1-08' YEAR(3) TO MONTH`	`'+001-08'`
Negative 5 months	INTERVAL MONTH	`INTERVAL '-5' MONTH(2)`	`'-5'`
Positive 14 months	INTERVAL MONTH	`INTERVAL '14' MONTH(2)`	`'+14'`
Negative 44 years and 11 months	INTERVAL YEAR TO MONTH	`INTERVAL '-44-11' YEAR(2) TO MONTH`	`'-44-11'`
Positive 11 days, 10 hours, and 9 minutes	INTERVAL DAY TO MINUTE	`INTERVAL '11 10:09' DAY(2) TO MINUTE`	`'+11 10:09'`
Positive 2 days, 23 hours, 8 minutes, 23 seconds, and 275 milliseconds	INTERVAL DAY TO SECOND	`INTERVAL '02 23:08:23.275' DAY(2) TO SECOND(3)`	`'+2 23:08:23.275'`
Positive 4 seconds and 300 milliseconds	INTERVAL SECOND	`INTERVAL '4.3' SECOND(5, 6)`	`'+4.300000'`

Operations that involve date and time values¶

The following table shows the data type of the result for valid arithmetic operations that involve interval values:


First operand	Operator	Second operand	Result type
Timestamp	`-`	Timestamp	An interval data type
Date or timestamp	`+`	Interval	DATE, DATETIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ
Date or timestamp	`-`	Interval	DATE, DATETIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ
Interval	`+`	Date or timestamp	DATE, DATETIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ
Numeric	`*`	Interval	An interval data type
Interval	`*`	Numeric	An interval data type
Interval	`/`	Numeric	An interval data type
Interval	`+`	Interval	An interval data type
Interval	`-`	Interval	An interval data type

For operations that involve two interval values, the values must both be year-month interval values, or they must both be day-time interval values. Operations that mix year-month interval values and day-time interval values aren't supported. When the operation involves two year-month interval values, the result type is a year-month interval type. When the operation involves two day-time interval values, the result type is a day-time interval type.

Functions that accept interval values as arguments¶

The following functions accept interval values as arguments:

Examples for interval data types¶

The following examples show how to use interval data types:

Performing arithmetic by using interval data
Inserting and querying year-month interval data
Inserting and querying day-time interval data
Copying interval data into a table and querying the table

Performing arithmetic by using interval data¶

The following examples perform arithmetic by using interval data.

Add one year and one month to a date:

SELECT TO_DATE('2024-01-01') + INTERVAL '1-1' YEAR TO MONTH
  AS date_plus_one_year_one_month;

+------------------------------+
| DATE_PLUS_ONE_YEAR_ONE_MONTH |
|------------------------------|
| 2025-02-01                   |
+------------------------------+

Subtract one year and one month from a date:

SELECT TO_DATE('2024-01-01') + INTERVAL '-1-1' YEAR TO MONTH
  AS date_plus_one_year_one_month;

+------------------------------+
| DATE_PLUS_ONE_YEAR_ONE_MONTH |
|------------------------------|
| 2022-12-01                   |
+------------------------------+

Add a period of time to a timestamp:

SELECT TO_TIMESTAMP('2024-01-01 08:08:08.99') + INTERVAL '1 01:01:01.7878' DAY TO SECOND
  AS date_plus_period_of_time;

+--------------------------+
| DATE_PLUS_PERIOD_OF_TIME |
|--------------------------|
| 2024-01-02 09:09:10.777  |
+--------------------------+

The following example uses the SYSTEM$TYPEOF function to show that an INTERVAL DAY TO SECOND value is returned when a query subtracts two timestamp values:

SELECT SYSTEM$TYPEOF(TO_TIMESTAMP('2025-10-05 01:02:03') - TO_TIMESTAMP('2025-09-15 11:36:22'))
  AS type;

+------------------------------------+
| TYPE                               |
|------------------------------------|
| INTERVAL DAY(9) TO SECOND(9)[SB16] |
+------------------------------------+

To view the results of the query in interval format, you can cast the expression to the INTERVAL DAY(2) TO SECOND(2) data type to specify precision, and then cast to VARCHAR:

SELECT (TO_TIMESTAMP('2025-10-05 01:02:03') - TO_TIMESTAMP('2025-09-15 11:36:22'))::INTERVAL DAY(2) TO SECOND(2)::VARCHAR
  AS interval_format_result;

+------------------------+
| INTERVAL_FORMAT_RESULT |
|------------------------|
| +19 13:25:41.00        |
+------------------------+

Inserting and querying year-month interval data¶

Create a table that tracks candidates for open positions with an INTERVAL YEAR TO MONTH column, and insert data:

CREATE OR REPLACE TABLE candidates (
  name_first VARCHAR,
  name_last VARCHAR,
  duration_of_experience INTERVAL YEAR(2) TO MONTH);

INSERT INTO candidates VALUES ('Jane', 'Smith', '14-4');
INSERT INTO candidates VALUES ('Robert', 'Adams', '0-3');
INSERT INTO candidates VALUES ('Mary', 'Jones', '5-11');

When you query the table without casting the duration_of_experience` column to a data type, the output shows the column values as the total number of months in each row:

SELECT name_first,
       name_last,
       duration_of_experience AS months_of_experience
  FROM candidates;

+------------+-----------+----------------------+
| NAME_FIRST | NAME_LAST | MONTHS_OF_EXPERIENCE |
|------------+-----------+----------------------|
| Jane       | Smith     |                  172 |
| Robert     | Adams     |                    3 |
| Mary       | Jones     |                   71 |
+------------+-----------+----------------------+

When you query the table and cast the duration_of_experience column to the VARCHAR data type, the output shows the column values in interval format:

SELECT name_first,
       name_last,
       duration_of_experience::VARCHAR AS duration_of_experience
  FROM candidates;

+------------+-----------+------------------------+
| NAME_FIRST | NAME_LAST | DURATION_OF_EXPERIENCE |
|------------+-----------+------------------------|
| Jane       | Smith     | +14-04                 |
| Robert     | Adams     | +0-03                  |
| Mary       | Jones     | +5-11                  |
+------------+-----------+------------------------+

Inserting and querying day-time interval data¶

Create a table that specifies the timeout duration for various software features with an INTERVAL HOUR TO SECOND column, and insert data:

CREATE OR REPLACE TABLE feature_timeouts (
  feature VARCHAR,
  timeout_duration INTERVAL HOUR(2) TO SECOND(0));

INSERT INTO feature_timeouts VALUES ('Feature1', '00:00:30');
INSERT INTO feature_timeouts VALUES ('Feature2', '00:10:00');
INSERT INTO feature_timeouts VALUES ('Feature3', '01:00:00');

Query the table and cast the timeout_duration column to the VARCHAR data type:

SELECT feature,
       timeout_duration::VARCHAR AS timeout_duration
  FROM feature_timeouts;

+----------+------------------+
| FEATURE  | TIMEOUT_DURATION |
|----------+------------------|
| Feature1 | +0:00:30         |
| Feature2 | +0:10:00         |
| Feature3 | +1:00:00         |
+----------+------------------+

Copying interval data into a table and querying the table¶

Complete the following steps to stage a file with interval data, and then copy the file into a table:

In a file on your file system, copy the following content:
```
1,1-2,28 16:15:14.0
2,-3-2,-54 16:15:14.123
```
This example assumes that the file is named interval_values.csv in the /examples/intervals/ directory.
Create a stage:
```
CREATE STAGE interval_stage;
```

In the internal staging location, stage the file:

PUT file:///examples/intervals/interval_values.csv @~/interval_stage
  AUTO_COMPRESS=false;

Create a table for the data:

CREATE OR REPLACE TABLE sample_interval_values(
  c1 STRING,
  c2 INTERVAL YEAR(1) TO MONTH,
  c3 INTERVAL DAY(2) TO SECOND(3));

To load the staged file into the table that you created, use the COPY INTO <table> command:
```
COPY INTO sample_interval_values FROM @~/interval_stage;
```

To view the loaded data, query the table, and cast to the VARCHAR type to view the loaded data:

SELECT c1,
       c2::VARCHAR AS YEAR_TO_MONTH,
       c3::VARCHAR AS DAY_TO_SECOND,
  FROM sample_interval_values;

+----+---------------+------------------+
| C1 | YEAR_TO_MONTH | DAY_TO_SECOND    |
|----+---------------+------------------|
| 1  | +1-02         | +28 16:15:14.000 |
| 2  | -3-02         | -54 16:15:14.123 |
+----+---------------+------------------+

Limitations for interval data types¶

The following limitations apply to interval data types:

Year-month interval values can't be combined or compared with day-time interval values.
Interval constants and values of interval data type can't be combined or compared.
Interval constants can't be inserted into a column that has an interval data type.
VARIANT values can't contain interval values.
Structured data type values can't contain interval values.
Interval expressions can't be used in user-defined functions (UDFs) or Snowflake Scripting.
The following types of tables can't have interval columns:
Queries on interval columns can't benefit from the 搜索优化服务.

TIME¶

Snowflake 支持以 HH:MI:SS 的形式存储时间的单一 TIME 数据类型。

TIME 支持用于小数秒的可选精度参数（例如 TIME(3)）。时间精度的范围为 0（秒）到 9（纳秒）。默认精度为 9。

所有 TIME 值必须介于 00:00:00 和 23:59:59.999999999 之间。TIME 在内部存储“挂钟”时间，对 TIME 值的所有操作都是在完全不考虑时区的前提下执行的。

TIMESTAMP_LTZ、TIMESTAMP_NTZ、TIMESTAMP_TZ¶

Snowflake supports three variations of timestamp:

TIMESTAMP_LTZ:

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

Synonymous with TIMESTAMP_LTZ:

TIMESTAMPLTZ
TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP_NTZ:

TIMESTAMP_NTZ 在内部存储具有指定精度的“挂钟”时间。所有操作的执行均完全不考虑时区。

如果输出格式包含时区，则显示 UTC 指示符 (Z)。

TIMESTAMP_NTZ 是 TIMESTAMP 的默认值。

Synonymous with TIMESTAMP_NTZ:

TIMESTAMPNTZ
TIMESTAMP WITHOUT TIME ZONE
DATETIME

TIMESTAMP_TZ:

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

Synonymous with TIMESTAMP_TZ:

TIMESTAMPTZ
TIMESTAMP WITH TIME ZONE

TIMESTAMP_TZ 值根据其 UTC 时间进行比较。例如，不同时区的不同时间如果具有相同的 UTC 时间，则其比较结果为 TRUE。

SELECT '2024-01-01 00:00:00 +0000'::TIMESTAMP_TZ = '2024-01-01 01:00:00 +0100'::TIMESTAMP_TZ;

注意

TIMESTAMP_TZ 当前仅存储给定时区的偏移，而非给定值创建时的实际时区。这对于夏令时尤其重要，因为 UTC 不使用夏令时。

例如，如果 TIMEZONE 参数设置为 "America/Los_Angeles"，则将值转换为给定年份的 1 月的 TIMESTAMP_TZ 会存储 -0800 的时区偏移。如果稍后为该值增加六个月，-0800 偏移将保留下来，即使在 7 月份，洛杉矶的该偏移仍为 -0700。这是因为在创建值后，实际时区信息 ("America/Los_Angeles") 不再可用。下面的代码示例展示了此行为：

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

+-------------------------------------+
| '2024-01-01 12:00:00'::TIMESTAMP_TZ |
|-------------------------------------|
| 2024-01-01 12:00:00.000 -0800       |
+-------------------------------------+

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

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

TIMESTAMP¶

Snowflake 中的 TIMESTAMP 是与一种 TIMESTAMP_* 变体关联的用户指定的别名。在使用 TIMESTAMP 的所有操作中，都会自动使用关联的 TIMESTAMP_* 变体。TIMESTAMP 数据类型从不会存储在表中。

与 TIMESTAMP 关联的 TIMESTAMP_* 变体由 TIMESTAMP_TYPE_MAPPING 会话参数指定。默认为 TIMESTAMP_NTZ。

所有时间戳变体及 TIMESTAMP 别名都支持用于小数秒的可选精度参数（例如 TIMESTAMP(3)）。时间戳精度的范围为 0（秒）到 9（纳秒）。默认精度为 9。

时间戳示例¶

以下示例使用不同的时间戳创建表。

首先，创建一个包含 TIMESTAMP 列（已映射到 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 | policy name | privacy domain |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| TS   | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

接下来，显式使用 TIMESTAMP 变体之一 (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 | policy name | privacy domain |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| TS   | TIMESTAMP_LTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

为不同时区使用 TIMESTAMP_LTZ：

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');

此查询显示洛杉矶时间为 1 月 2 日 08:00（UTC 时间是 16:00）：

SELECT ts, HOUR(ts) FROM ts_test;

+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 |       16 |
| 2024-01-02 08:00:00.000 -0800 |        8 |
+-------------------------------+----------+

接下来，注意时间会随着时区的不同而变化：

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, HOUR(ts) FROM ts_test;

+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 19:00:00.000 -0500 |       19 |
| 2024-01-02 11:00:00.000 -0500 |       11 |
+-------------------------------+----------+

创建表并使用 TIMESTAMP_NTZ：

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_NTZ);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');

请注意，来自不同时区的两个时间都转换为相同的“挂钟”时间：

SELECT ts, HOUR(ts) FROM ts_test;

+-------------------------+----------+
| TS                      | HOUR(TS) |
|-------------------------+----------|
| 2024-01-01 16:00:00.000 |       16 |
| 2024-01-02 16:00:00.000 |       16 |
+-------------------------+----------+

接下来，请注意更改会话时区不会影响结果：

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, HOUR(ts) FROM ts_test;

+-------------------------+----------+
| TS                      | HOUR(TS) |
|-------------------------+----------|
| 2024-01-01 16:00:00.000 |       16 |
| 2024-01-02 16:00:00.000 |       16 |
+-------------------------+----------+

创建表并使用 TIMESTAMP_TZ：

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_TZ);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');

请注意，1 月 1 日的记录继承了会话时区，并且 America/Los_Angeles 被转换为数字时区偏移：

SELECT ts, HOUR(ts) FROM ts_test;

+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 |       16 |
| 2024-01-02 16:00:00.000 +0000 |       16 |
+-------------------------------+----------+

接下来，请注意更改会话时区不会影响结果：

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, HOUR(ts) FROM ts_test;

+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 |       16 |
| 2024-01-02 16:00:00.000 +0000 |       16 |
+-------------------------------+----------+

支持的日历¶

Snowflake 为所有日期和时间戳使用公历。公历从 1582 年开始，但可以识别以前的年份，这一点非常重要，因为 Snowflake 不会为了匹配儒略历而调整 1582 年之前的日期（或涉及 1582 年之前日期的计算）。UUUU 格式元素支持负数年份。

日期和时间格式¶

所有这些数据类型都接受最无歧义的日期、时间或日期 + 时间格式。请参阅 AUTO 检测支持的格式，了解 Snowflake 在配置为自动检测格式时可识别的格式。

您也可以手动指定日期和时间格式。在指定格式时，可使用下表中列出的不区分大小写的元素：


格式元素	描述
`YYYY`	四位数 ^[1] 年。
`YY`	两位数 ^[1] 年，由 TWO_DIGIT_CENTURY_START 会话参数控制，例如，当设置为 `1980` 时，`79` 和 `80` 这两个值将分别解析为 `2079` 和 `1980`。
`Y`	One-digit or two-digit ^[2] year without leading zeros, controlled by the TWO_DIGIT_CENTURY_START session parameter. For example, when the parameter set to `1990`, values of `2005` and `1991` are serialized as `5` and `91`, respectively.
`MM`	两位数 ^[1] 月（`01` = 1 月，依此类推）。
`MO`	One-digit or two-digit ^[2] month without leading zeros (`1` = January, and so on).
`MON`	Abbreviated month name ^[3].
`MMMM`	Full month name ^[3].
`DD`	两位数 ^[1] 一个月中的某一天（`01` 至 `31`）。
`D`	One-digit or two-digit ^[2] day of month without leading zeros (`1` through `31`).
`DY`	星期的缩写。
`HH24`	Two digits ^[1] for hour (`00` through `23`). You must not specify `AM` / `PM` or `A` / `P`.
`HH12`	Two digits ^[1] for hour (`01` through `12`). You can specify `AM` / `PM` or `A` / `P`.
`H24`	One or two digits ^[2] for hour without leading zeros (`0` through `23`). You must not specify `AM` / `PM` or `A` / `P`.
`H12`	One or two digits ^[2] for hour without leading zeros (`1` through `12`). You can specify `AM` / `PM` or `A` / `P`.
`AM`、`PM`	Ante meridiem (`AM`) / post meridiem (`PM`). Use this only with `HH12` and code:`H12` (not with `HH24` or `H24`).
`P`	Ante meridiem (`A`) / post meridiem (`P`). Use this only with `HH12` and code:`H12` (not with `HH24` or `H24`).
`HH`	Synonym for `HH24`.
`H`	Synonym for `H24`.
`MI`	两位数 ^[1] 分钟（`00` 至 `59`）。
`ME`	One or two digits ^[2] for minute without leading zeros (`0` through `59`).
`SS`	两位数 ^[1] 秒（`00` 至 `59`）。
`S`	One or two digits ^[2] for second without leading zeros (`0` through `59`).
`FF[0-9]`	精度为 ``0``（秒）至 ``9``（纳秒）的小数秒，例如 `FF`、`FF0`、`FF3`、`FF9`。指定 `FF` 等同于 `FF9` （纳秒）。
`TZH:TZM`、`TZHTZM`、`TZH`	两位数 ^[1] 时区小时和分钟，从 UTC 开始偏移。可以用 `+`/`-` 作为符号前缀。
`UUUU`	四位数年份，格式为 ISO (link removed)，负数为 BCE 年份。

_{[1] 位数描述了将值序列化为文本时生成的输出。解析文本时，Snowflake 最多可接受的指定位数。例如，天数可以是一位或两位数。}

_{[2] The number of digits describes the output produced when serializing values to text. Parsing isn't supported. If parsing is required, use an equivalent format that includes leading zeros. These format elements will be enabled in BCR bundle 2026_03.}

_{[3] For the MON format element, the output produced when serializing values to text is the abbreviated month name. For the MMMM format element, the output produced when serializing values to text is the full month name. When parsing text, Snowflake accepts the three-digit abbreviation or the full month name for both MON and MMMM. For example, "January" or "Jan", "February" or "Feb", and so on are accepted when parsing text.}

备注

当使用仅日期格式时，关联时间假定为当天的午夜。
Anything in the format between double quotes or other than the above elements is parsed/formatted without being interpreted. Snowflake recommends always enclosing literal characters in double quotes (for example, "T", "EST", "Z") to ensure they are treated as literals.
有关有效范围、位数和最佳实践的更多详细信息，请参阅有关使用日期、时间和时间戳格式的其他信息。

使用日期和时间格式的示例¶

以下示例使用 FF 指示输出在小数秒字段中有 9 位数：

CREATE OR REPLACE TABLE timestamp_demo_table(
  tstmp TIMESTAMP,
  tstmp_tz TIMESTAMP_TZ,
  tstmp_ntz TIMESTAMP_NTZ,
  tstmp_ltz TIMESTAMP_LTZ);
INSERT INTO timestamp_demo_table (tstmp, tstmp_tz, tstmp_ntz, tstmp_ltz) VALUES (
  '2024-03-12 01:02:03.123456789',
  '2024-03-12 01:02:03.123456789',
  '2024-03-12 01:02:03.123456789',
  '2024-03-12 01:02:03.123456789');

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

SELECT tstmp, tstmp_tz, tstmp_ntz, tstmp_ltz
  FROM timestamp_demo_table;

+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| TSTMP                         | TSTMP_TZ                      | TSTMP_NTZ                     | TSTMP_LTZ                     |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+

日期和时间常量¶

常量*（也称为 *字面量）是固定的数据值。Snowflake 支持使用字符串常量指定固定的日期、时间或时间戳值。字符串常量必须始终置于分隔符之间。Snowflake 支持使用单引号来分隔字符串常量。

例如：

DATE '2024-08-14'
TIME '10:03:56'
TIMESTAMP '2024-08-15 10:59:43'

字符串根据数据类型的输入格式解析为 DATE、TIME 或 TIMESTAMP 值，通过以下参数设置：

DATE:: DATE_INPUT_FORMAT
TIME:: TIME_INPUT_FORMAT
TIMESTAMP:: TIMESTAMP_INPUT_FORMAT

例如，要将特定日期插入到一个表的某个列中：

CREATE TABLE t1 (d1 DATE);

INSERT INTO t1 (d1) VALUES (DATE '2024-08-15');

间隔时间常量¶

您可以使用间隔时间常量在日期、时间或时间戳之间添加或减去特定时间段。间隔时间常量使用 INTERVAL 关键字实现，具有以下语法：

{ + | - } INTERVAL '<integer> [ <date_time_part> ] [ , <integer> [ <date_time_part> ] ... ]'

与所有字符串常量一样，Snowflake 需要用单引号来括起间隔时间常量。

备注

Interval constants support date and time arithmetic, but they don't support interval storage as a column type. To store interval values in a column, you can use interval data types.

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

INTERVAL '1 year' 表示一年。
INTERVAL '4 years, 5 months, 3 hours' 表示四年五个月零三个小时。

如果未指定日期或时间部分，则间隔时间表示的是秒（例如，INTERVAL '2' 与 INTERVAL '2 seconds' 相同）。请注意，这与执行日期算术运算的默认时间单位不同。有关更多详细信息，请参阅日期的简单算术运算。

有关受支持的日期和时间部分的列表，请参阅时间间隔支持的日期和时间部分。

备注

间隔时间增量的顺序很重要。增量按列出的顺序加减。例如：

INTERVAL '1 year, 1 day' 首先加或减一年，然后加或减一天。

INTERVAL '1 day, 1 year' 首先加或减一天，然后加或减一年。

排序差异可能会影响受日历事件（如闰年）影响的计算：
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 不是数据类型（也就是说，您不能将表列定义为数据类型 INTERVAL）。间隔时间只能用于日期、时间和时间戳算术运算。
您不能将间隔时间用于 SQL 变量。例如，以下查询会返回错误：
```
SET v1 = '1 year';

SELECT TO_DATE('2023-04-15') + INTERVAL $v1;
```

间隔时间支持的日期和时间部分¶

INTERVAL 关键字支持将以下日期和时间部分作为实参（不区分大小写）：


日期或时间部分	缩写/变体
`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`

间隔时间示例¶

为特定日期加上一个年份间隔时间：

SELECT TO_DATE('2023-04-15') + INTERVAL '1 year';

+-------------------------------------------+
| TO_DATE('2023-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2024-04-15                                |
+-------------------------------------------+

将 3 小时 18 分钟的间隔时间添加到特定时间：

SELECT TO_TIME('04:15:29') + INTERVAL '3 hours, 18 minutes';

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

在 CURRENT_TIMESTAMP 函数的输出中加上一个复杂的时间间隔：

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_INTERVAL1             |
|-------------------------------|
| 2026-11-07 18:07:19.875000001 |
+-------------------------------+

为特定日期加上一个复杂时间间隔（日期/时间部分使用缩写表示法）：

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

查询员工信息表，返回过去两年三个月内录用的员工姓名：

SELECT name, hire_date
  FROM employees
  WHERE hire_date > CURRENT_DATE - INTERVAL '2 y, 3 month';

从名为 t1 的表中筛选出名为 ts 的 TIMESTAMP 列，并为每个返回值加上四秒：

SELECT ts + INTERVAL '4 seconds'
  FROM t1
  WHERE ts > TO_TIMESTAMP('2024-04-05 01:02:03');

日期的简单算术运算¶

除了使用间隔时间常量为日期、时间和时间戳进行加减之外，您还能以 { + | - } integer 的形式对 DATE 值进行天数的加减，其中 integer 指定要加/减的天数。

备注

TIME 和 TIMESTAMP 值尚不支持简单算术运算。

日期算术运算示例¶

为特定日期加一天：

SELECT TO_DATE('2024-04-15') + 1;

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

为特定日期减四天：

SELECT TO_DATE('2024-04-15') - 4;

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

查询名为 employees 的表，并返回已离职但受雇时间超过 365 天的人员的姓名：

SELECT name
  FROM employees
  WHERE end_date > start_date + 365;