日期和时间数据类型¶

DATE¶

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

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

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

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

DATETIME¶

DATETIME 是 TIMESTAMP_NTZ 的别名。

TIME¶

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

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

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

TIMESTAMP¶

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

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

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

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

TIMESTAMP_LTZ、TIMESTAMP_NTZ、TIMESTAMP_TZ¶

Snowflake 支持三种时间戳变体。

TIMESTAMP_LTZ:

TIMESTAMP_LTZ 在内部存储具有指定精度的 UTC 时间。但所有操作均在当前会话的时区中执行，由 TIMEZONE 会话参数控制。

TIMESTAMP_LTZ 的别名：

• TIMESTAMPLTZ

• TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP_NTZ:

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

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

TIMESTAMP_NTZ 是 TIMESTAMP 的默认值。

TIMESTAMP_NTZ 的别名：

• TIMESTAMPNTZ

• TIMESTAMP WITHOUT TIME ZONE

TIMESTAMP_TZ:

TIMESTAMP_TZ 在内部存储 UTC 时间及关联的 时区偏移。如未提供时区，则使用会话时区偏移。所有操作均使用特定于每条记录的时区偏移执行。

TIMESTAMP_TZ 的别名：

• TIMESTAMPTZ

• TIMESTAMP WITH TIME ZONE

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

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

Copy

时间戳示例¶

使用不同的时间戳创建表：

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

为不同时区使用 TIMESTAMP_LTZ：

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

使用 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

使用 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

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

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

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

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

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

间隔时间示例¶

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

select to_date('2018-04-15') + INTERVAL '1 year';

+-------------------------------------------+
| TO_DATE('2018-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2019-04-15                                |
+-------------------------------------------+

Copy

为特定时间加上 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                                             |
+------------------------------------------------------+

Copy

在 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_INTERVAL              |
|-------------------------------|
| 2020-12-28 08:08:01.325 -0800 |
+-------------------------------+

Copy

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

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

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

select name, hire_date from employees where hire_date > current_date - INTERVAL '2 y, 3 month';

Copy

从名为 tl 的表中筛选出名为 ts 的时间戳列，并为每个返回值加上 4 秒：

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

Copy

日期算术运算示例¶

为特定日期加 1 天：

select to_date('2018-04-15') + 1;

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

Copy

为特定日期减 4 天：

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

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

Copy

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

select name from employees where end_date > start_date + 365;

Copy