使用日期和时间值

日期和时间计算是分析和数据挖掘中广泛使用的关键计算之一。本主题提供常见日期和时间查询和计算的实际示例。

加载日期和时间戳

本节提供了加载日期和时间戳戳值的示例，并介绍了加载这些值时与时区相关的注意事项。

加载未附加时区的时间戳

在以下示例中，TIMESTAMP_TYPE_MAPPING 参数设置为 TIMESTAMP_LTZ （本地时区）。TIMEZONE 参数设置为 America/Chicago 时间。如果某些传入时间戳未指定时区，则 Snowflake 将加载这些字符串，假设时间戳表示为 TIMEZONE 参数所设置时区中的本地时间。

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-05-01 00:00:00.000');

SELECT * FROM time;
Copy
+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-05-01 00:00:00.000 -0500 |
+-------------------------------+

加载已附加时区的时间戳

在以下示例中，TIMESTAMP_TYPE_MAPPING 参数设置为 TIMESTAMP_LTZ （本地时区）。TIMEZONE 参数设置为 America/Chicago 时间。如果某些传入的时间戳指定的时区不同，Snowflake 将以 America/Chicago 时间加载该字符串。

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-04-30 19:00:00.000 -0800');

SELECT * FROM time;
Copy
+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-04-30 22:00:00.000 -0500 |
+-------------------------------+

将时间戳转换为替代时区

在以下示例中，存储了一组时间戳值，没有时区数据。时间戳按 UTC 时间加载并转换为其他时区：

ALTER SESSION SET TIMEZONE = 'UTC';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';

CREATE OR REPLACE TABLE utctime (ntz TIMESTAMP_NTZ);
INSERT INTO utctime VALUES ('2024-05-01 00:00:00.000');
Copy
SELECT * FROM utctime;
Copy
+-------------------------+
| NTZ                     |
|-------------------------|
| 2024-05-01 00:00:00.000 |
+-------------------------+

SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::TIMESTAMP_LTZ AS ChicagoTime
  FROM utctime;
Copy
+---------------------------+
| CHICAGOTIME               |
|---------------------------|
| 2024-04-30 19:00:00 +0000 |
+---------------------------+

SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::TIMESTAMP_LTZ AS LATime
  FROM utctime;
Copy
+---------------------------+
| LATIME                    |
|---------------------------|
| 2024-04-30 17:00:00 +0000 |
+---------------------------+

将有效的日期字符串插入到表的日期列中

此示例将值插入到 DATE 列中。

CREATE OR REPLACE TABLE my_table(id INTEGER, date1 DATE);
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2024.07.23', 'YYYY.MM.DD'));
INSERT INTO my_table(id) VALUES (2);
Copy
SELECT id, date1
  FROM my_table
  ORDER BY id;
Copy
+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2024-07-23 |
|  2 | NULL       |
+----+------------+

TO_DATE 函数接受 TIMESTAMP 格式中的 TIMESTAMP 值甚至字符串，但丢弃时间信息（小时、分钟等）。

INSERT INTO my_table(id, date1) VALUES
  (3, TO_DATE('2024.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
  (4, TO_TIMESTAMP('2024.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));
Copy
SELECT id, date1
  FROM my_table
  WHERE id >= 3;
Copy
+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2024-02-20 |
|  4 | 2024-02-24 |
+----+------------+

如果插入仅使用时间定义的日期 DATE，则默认日期为 1970 年 1 月 1 日。

INSERT INTO my_table(id, date1) VALUES
  (5, TO_DATE('11:20:30', 'hh:mi:ss'));
Copy
SELECT id, date1
  FROM my_table
  WHERE id = 5;
Copy
+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+

检索 DATE 值时，您可以将其格式调整为 TIMESTAMP 值：

SELECT id,
       TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
  FROM my_table
  ORDER BY id;
Copy
+----+----------------------+
| ID | DATE1                |
|----+----------------------|
|  1 | 23-Jul-2024 00:00:00 |
|  2 | NULL                 |
|  3 | 20-Feb-2024 00:00:00 |
|  4 | 24-Feb-2024 00:00:00 |
|  5 | 01-Jan-1970 00:00:00 |
+----+----------------------+

检索当前日期和时间

获取当前日期作为 DATE 值：

SELECT CURRENT_DATE();
Copy

以 TIMESTAMP 值形式获取当前日期和时间：

SELECT CURRENT_TIMESTAMP();
Copy

检索日期和星期几

使用 EXTRACT 函数获取当前星期几的数字：

SELECT EXTRACT('dayofweek', CURRENT_DATE());
Copy

备注

  • dayofweek_iso 部分遵循 ISO-8601 数据元素和交换格式标准。函数返回一个 1-7 范围内的整数值，其中 1 表示星期一。

  • 为了与其他一些系统兼容，dayofweek 部分遵循 UNIX 标准。函数返回一个 0-6 范围内的整数值，其中 0 表示星期日。

使用 TO_VARCHAR 或 DECODE 函数以字符串形式获取当前星期几：

运行一个查询，返回当前日期的简短英文名称（例如：Sun、Mon 等）：

SELECT TO_VARCHAR(CURRENT_DATE(), 'dy');
Copy

运行一个查询，返回当前日期的显式提供的星期名称：

SELECT DECODE(EXTRACT('dayofweek_iso', CURRENT_DATE()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday') AS weekday_name;
Copy

检索日期和时间部分

使用 DATE_PART 函数获取当前日期和时间的各种日期和时间部分。

查询本月的当前日期：

SELECT DATE_PART(day, CURRENT_TIMESTAMP());
Copy

查询当前年份：

SELECT DATE_PART(year, CURRENT_TIMESTAMP());
Copy

查询当前月：

SELECT DATE_PART(month, CURRENT_TIMESTAMP());
Copy

查询当前小时：

SELECT DATE_PART(hour, CURRENT_TIMESTAMP());
Copy

查询当前分钟：

SELECT DATE_PART(minute, CURRENT_TIMESTAMP());
Copy

查询当前秒：

SELECT DATE_PART(second, CURRENT_TIMESTAMP());
Copy

您还可以使用 EXTRACT 函数获取当前日期和时间的各种日期和时间部分。

查询本月的当前日期：

SELECT EXTRACT('day', CURRENT_TIMESTAMP());
Copy

查询当前年份：

SELECT EXTRACT('year', CURRENT_TIMESTAMP());
Copy

查询当前月：

SELECT EXTRACT('month', CURRENT_TIMESTAMP());
Copy

查询当前小时：

SELECT EXTRACT('hour', CURRENT_TIMESTAMP());
Copy

查询当前分钟：

SELECT EXTRACT('minute', CURRENT_TIMESTAMP());
Copy

查询当前秒：

SELECT EXTRACT('second', CURRENT_TIMESTAMP());
Copy

此查询返回当前日期和时间的各种日期和时间部分的表格输出：

SELECT month(CURRENT_TIMESTAMP()) AS month,
       day(CURRENT_TIMESTAMP()) AS day,
       hour(CURRENT_TIMESTAMP()) AS hour,
       minute(CURRENT_TIMESTAMP()) AS minute,
       second(CURRENT_TIMESTAMP()) AS second;
Copy
+-------+-----+------+--------+--------+
| MONTH | DAY | HOUR | MINUTE | SECOND |
|-------+-----+------+--------+--------|
|     8 |  28 |    7 |     59 |     28 |
+-------+-----+------+--------+--------+

计算业务日历日期和时间

使用 DATE_TRUNC 函数获取该月的第一天作为 DATE 值。例如，获取当月的第一天：

SELECT DATE_TRUNC('month', CURRENT_DATE());
Copy

使用 DATEADD 和 DATE_TRUNC 函数获取当前月份的最后一天作为 DATE 值：

SELECT DATEADD('day',
               -1,
               DATE_TRUNC('month', DATEADD(day, 31, DATE_TRUNC('month',CURRENT_DATE()))));
Copy

还有另一个选项，在下面的示例中，使用 DATE_TRUNC 检索当前月份的开始，加上一个月以检索下一个月的开始，然后减去一天以确定当前月份的最后一天。

SELECT DATEADD('day',
               -1,
               DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())));
Copy

获取上个月的最后一天作为 DATE 值：

SELECT DATEADD(day,
               -1,
               DATE_TRUNC('month', CURRENT_DATE()));
Copy

获取当前月份的简短英文名称（例如：Jan、``Dec``等）：

SELECT TO_VARCHAR(CURRENT_DATE(), 'Mon');
Copy

使用显式提供的月份名称获取当前月份名称：

SELECT DECODE(EXTRACT('month', CURRENT_DATE()),
         1, 'January',
         2, 'February',
         3, 'March',
         4, 'April',
         5, 'May',
         6, 'June',
         7, 'July',
         8, 'August',
         9, 'September',
         10, 'October',
         11, 'November',
         12, 'December') AS current_month;
Copy

获取本周星期一的日期：

SELECT DATEADD('day',
               (EXTRACT('dayofweek_iso', CURRENT_DATE()) * -1) + 1,
               CURRENT_DATE());
Copy

获取本周星期五的日期：

SELECT DATEADD('day',
               (5 - EXTRACT('dayofweek_iso', CURRENT_DATE())),
               CURRENT_DATE());
Copy

使用 DATE_PART 函数获取当前月份第一个星期一的日期：

SELECT DATEADD(day,
               MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', CURRENT_DATE())), 7),
               DATE_TRUNC('month', CURRENT_DATE()));
Copy

备注

在上面的查询中，7 + 1 中的 1 值转换为星期一。要检索第一个星期二、星期三等的日期，请分别用 2, 3 等替换，直到 7 替换 Sunday。

获取当前年份的第一天作为 DATE 值：

SELECT DATE_TRUNC('year', CURRENT_DATE());
Copy

获取当前年份的最后一天作为 DATE 值：

SELECT DATEADD('day',
               -1,
               DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE())));
Copy

获取上一年的最后一天作为 DATE 值：

SELECT DATEADD('day',
               -1,
               DATE_TRUNC('year', CURRENT_DATE()));
Copy

获取当前季度的第一天作为 DATE 值：

SELECT DATE_TRUNC('quarter', CURRENT_DATE());
Copy

获取当前季度的最后一天作为 DATE 值：

SELECT DATEADD('day',
               -1,
               DATEADD('month', 3, DATE_TRUNC('quarter', CURRENT_DATE())));
Copy

获取当天午夜的日期和时间戳：

SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP());
Copy
+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700        |
+----------------------------------------+

递增日期和时间值

使用 DATEADD 函数递增日期和时间值。

在当前日期的基础上增加两年：

SELECT DATEADD(year, 2, CURRENT_DATE());
Copy

在当前日期的基础上增加两天：

SELECT DATEADD(day, 2, CURRENT_DATE());
Copy

在当前日期和时间的基础上增加两个小时：

SELECT DATEADD(hour, 2, CURRENT_TIMESTAMP());
Copy

在当前日期和时间的基础上增加两分钟：

SELECT DATEADD(minute, 2, CURRENT_TIMESTAMP());
Copy

在当前日期和时间的基础上增加两秒钟：

SELECT DATEADD(second, 2, CURRENT_TIMESTAMP());
Copy

将有效的字符串转换为日期、时间或时间戳

在大多数用例中，Snowflake 可以正确处理格式化为字符串的日期和时间戳值。在某些情况下，例如基于字符串的比较，或者当结果依赖于与会话参数中设置的时间戳格式不同的时候，我们建议显式地将值转换为所需的格式，以避免意外的结果。

例如，在没有显式强制类型转换的情况下，比较字符串值会生成基于字符串的结果：

CREATE OR REPLACE TABLE timestamps(timestamp1 STRING);

INSERT INTO timestamps VALUES
  ('Fri, 05 Apr 2013 00:00:00 -0700'),
  ('Sat, 06 Apr 2013 00:00:00 -0700'),
  ('Sat, 01 Jan 2000 00:00:00 -0800'),
  ('Wed, 01 Jan 2020 00:00:00 -0800');
Copy

以下查询执行比较，但不执行显式类型转换：

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';
Copy
+------------+
| TIMESTAMP1 |
|------------|
+------------+

以下查询执行与显式类型转换的 DATE 进行比较：

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::DATE;
Copy
+---------------------------------+
| DATE1                           |
|---------------------------------|
| Fri, 05 Apr 2013 00:00:00 -0700 |
| Sat, 06 Apr 2013 00:00:00 -0700 |
| Sat, 01 Jan 2000 00:00:00 -0800 |
+---------------------------------+

有关转换函数的更多信息，请参阅 转换函数中的日期和时间格式。

将日期算术应用于日期字符串

在字符串中表示的日期上增加五天：

SELECT DATEADD('day',
               5,
               TO_TIMESTAMP('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'))
  AS add_five_days;
Copy
+-------------------------+
| ADD_FIVE_DAYS           |
|-------------------------|
| 2024-01-17 00:00:00.000 |
+-------------------------+

您可以使用 DATEDIFF 函数计算当前日期与字符串中表示的日期之间的天数差：

使用 TO_TIMESTAMP 函数计算天数差：

SELECT DATEDIFF('day',
                TO_TIMESTAMP ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
                CURRENT_DATE())
  AS to_timestamp_difference;
Copy
+-------------------------+
| TO_TIMESTAMP_DIFFERENCE |
|-------------------------|
|                     229 |
+-------------------------+

使用 TO_DATE 函数计算天数差：

SELECT DATEDIFF('day',
                TO_DATE ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
                CURRENT_DATE())
  AS to_date_difference;
Copy
+--------------------+
| TO_DATE_DIFFERENCE |
|--------------------|
|                229 |
+--------------------+

在指定日期上增加一天：

SELECT TO_DATE('2024-01-15') + 1 AS date_plus_one;
Copy
+---------------+
| DATE_PLUS_ONE |
|---------------|
| 2024-01-16    |
+---------------+

从当前日期减去九天（例如，2024 年 8 月 28 日）：

SELECT CURRENT_DATE() - 9 AS date_minus_nine;
Copy
+-----------------+
| DATE_MINUS_NINE |
|-----------------|
| 2024-08-19      |
+-----------------+

计算日期或时间之间的差异

计算当前日期与三年内日期之间的差值：

SELECT DATEDIFF(year, CURRENT_DATE(),
       DATEADD(year, 3, CURRENT_DATE()));
Copy

计算当前日期与三个月后的日期之间的差值：

SELECT DATEDIFF(month, CURRENT_DATE(),
       DATEADD(month, 3, CURRENT_DATE()));
Copy

计算当前日期与三天后的日期之间的差值：

SELECT DATEDIFF(day, CURRENT_DATE(),
       DATEADD(day, 3, CURRENT_DATE()));
Copy

计算当前时间与三个小时后的时间的差值：

SELECT DATEDIFF(hour, CURRENT_TIMESTAMP(),
       DATEADD(hour, 3, CURRENT_TIMESTAMP()));
Copy

计算当前时间与三分钟后的时间的差值：

SELECT DATEDIFF(minute, CURRENT_TIMESTAMP(),
       DATEADD(minute, 3, CURRENT_TIMESTAMP()));
Copy

计算当前时间与三秒后的时间的差值:

SELECT DATEDIFF(second, CURRENT_TIMESTAMP(),
       DATEADD(second, 3, CURRENT_TIMESTAMP()));
Copy

创建年历视图

CREATE OR REPLACE VIEW calendar_2016 AS
  SELECT n,
         theDate,
         DECODE (EXTRACT('dayofweek',theDate),
           1 , 'Monday',
           2 , 'Tuesday',
           3 , 'Wednesday',
           4 , 'Thursday',
           5 , 'Friday',
           6 , 'Saturday',
           0 , 'Sunday') theDayOfTheWeek,
         DECODE (EXTRACT(month FROM theDate),
           1 , 'January',
           2 , 'February',
           3 , 'March',
           4 , 'April',
           5 , 'May',
           6 , 'June',
           7 , 'July',
           8 , 'August',
           9 , 'september',
           10, 'October',
           11, 'November',
           12, 'December') theMonth,
         EXTRACT(year FROM theDate) theYear
  FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY seq4()) AS n,
            DATEADD(day, ROW_NUMBER() OVER (ORDER BY seq4())-1, TO_DATE('2016-01-01')) AS theDate
      FROM table(generator(rowCount => 365)))
  ORDER BY n ASC;

SELECT * from CALENDAR_2016;
Copy
+-----+------------+-----------------+-----------+---------+
|   N | THEDATE    | THEDAYOFTHEWEEK | THEMONTH  | THEYEAR |
|-----+------------+-----------------+-----------+---------|
|   1 | 2016-01-01 | Friday          | January   |    2016 |
|   2 | 2016-01-02 | Saturday        | January   |    2016 |
  ...
| 364 | 2016-12-29 | Thursday        | December  |    2016 |
| 365 | 2016-12-30 | Friday          | December  |    2016 |
+-----+------------+-----------------+-----------+---------+
语言: 中文