使用日期和时间值¶
日期和时间计算是分析和数据挖掘中广泛使用的关键计算之一。本主题提供常见日期和时间查询和计算的实际示例。
本主题内容:
加载日期和时间戳¶
加载未附加时区的时间戳¶
在以下示例中,TIMESTAMP_TYPE_MAPPING 参数设置为 TIMESTAMP_LTZ (本地时区)。TIMEZONE 参数设置为美国/芝加哥时间。如果一组传入时间戳未指定时区,则 Snowflake 将加载这些字符串,假设时间戳表示设置时区中的本地时间。
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 ('2016-05-01 00:00:00.000');
SELECT * FROM time;
+---------------------------------+
| LTZ |
|---------------------------------|
| Sun, 01 May 2016 00:00:00 -0500 |
+---------------------------------+
加载已附加时区的时间戳¶
在以下示例中,TIMESTAMP_TYPE_MAPPING 参数设置为 TIMESTAMP_LTZ (本地时区)。TIMEZONE 参数设置为美国/芝加哥时间。假设一组传入时间戳指定了不同的时区。Snowflake 以美国/芝加哥时间加载字符串。
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 ('2016-04-30 19:00:00.000 -0800');
SELECT * FROM time;
+---------------------------------+
| LTZ |
|---------------------------------|
| Sat, 30 Apr 2016 22:00:00 -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 ('2016-05-01 00:00:00.000');
SELECT * FROM utctime;
SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::timestamp_ltz AS ChicagoTime
FROM utctime;
SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::timestamp_ltz AS LATime
FROM utctime;
将有效的日期字符串插入到表的日期列中¶
此示例演示如何将值插入到 DATE 列中。
CREATE TABLE my_table(id INTEGER, date1 DATE); -- July 23, 2016. INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2016.07.23', 'YYYY.MM.DD')); -- NULL. INSERT INTO my_table(id) VALUES (2);SELECT id, date1 FROM my_table ORDER BY id; +----+------------+ | ID | DATE1 | |----+------------| | 1 | 2016-07-23 | | 2 | NULL | +----+------------+
TO_DATE 函数接受 TIMESTAMP 格式中的 TIMESTAMP 值甚至字符串,但丢弃时间信息(小时、分钟等)。
INSERT INTO my_table(id, date1) VALUES (3, TO_DATE('2020.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')), (4, TO_TIMESTAMP('2020.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));SELECT id, date1 FROM my_table WHERE id >= 3; +----+------------+ | ID | DATE1 | |----+------------| | 3 | 2020-02-20 | | 4 | 2020-02-24 | +----+------------+
如果插入仅使用时间定义的日期 DATE,则默认日期为 1970 年 1 月 1 日。
INSERT INTO my_table(id, date1) VALUES (5, TO_DATE('11:20:30', 'hh:mi:ss'));SELECT id, date1 FROM my_table WHERE id = 5; +----+------------+ | 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; +----+----------------------+ | ID | DATE1 | |----+----------------------| | 1 | 23-Jul-2016 00:00:00 | | 2 | NULL | | 3 | 20-Feb-2020 00:00:00 | | 4 | 24-Feb-2020 00:00:00 | | 5 | 01-Jan-1970 00:00:00 | +----+----------------------+
检索当前日期和时间¶
获取当前日期作为 DATE 值:
SELECT current_date();
以 TIMESTAMP 值形式获取当前日期和时间:
SELECT current_timestamp();
检索日期和星期几¶
使用 EXTRACT 函数获取当前星期几的数字:
SELECT EXTRACT('dayofweek',current_date());
备注
dayofweek_iso
部分遵循 ISO-8601 数据元素和交换格式标准。函数返回一个 1-7 范围内的整数值,其中 1 表示星期一。为了与其他一些系统兼容,
dayofweek
部分遵循 UNIX 标准。函数返回一个 0-6 范围内的整数值,其中 0 表示星期日。
使用 TO_VARCHAR 或 DECODE 函数以字符串形式获取当前星期几:
-- Output short English names, e.g. "Sun", "Mon" etc. SELECT TO_VARCHAR(current_date(), 'DY'); -- Output arbitrary, explicitly-provided weekday names: SELECT DECODE(EXTRACT ('dayofweek_iso',current_date()), 1, 'Monday', 2, 'Tuesday', 3, 'Wednesday', 4, 'Thursday', 5, 'Friday', 6, 'Saturday', 7, 'Sunday');
检索日期和时间部分¶
使用 DATE_PART 函数获取当前日期和时间的各种日期和时间部分:
-- Current day of the month SELECT DATE_PART(day, current_timestamp()); -- Current year SELECT DATE_PART(year, current_timestamp()); -- Current month SELECT DATE_PART(month, current_timestamp()); -- Current hour SELECT DATE_PART(hour, current_timestamp()); -- Current minute SELECT DATE_PART(minute, current_timestamp()); -- Current second SELECT DATE_PART(second, current_timestamp());
使用 EXTRACT 函数的替代选项:
-- Current day of the month SELECT EXTRACT('day', current_timestamp()); -- Current year SELECT EXTRACT('year', current_timestamp()); -- Current month SELECT EXTRACT('month', current_timestamp()); -- Current hour SELECT EXTRACT('hour', current_timestamp()); -- Current minute SELECT EXTRACT('minute', current_timestamp()); -- Current second SELECT EXTRACT('second', current_timestamp());
替代表格输出:
SELECT day(current_timestamp() ) , hour( current_timestamp() ) , second(current_timestamp()) , minute(current_timestamp()) , month(current_timestamp()); +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+ | DAY(CURRENT_TIMESTAMP() ) | HOUR( CURRENT_TIMESTAMP() ) | SECOND(CURRENT_TIMESTAMP()) | MINUTE(CURRENT_TIMESTAMP()) | MONTH(CURRENT_TIMESTAMP()) | |---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------| | 7 | 6 | 43 | 44 | 9 | +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+
计算业务日历日期和时间¶
使用 DATE_TRUNC 函数获取该月的第一天作为 DATE 值。例如,获取当月的第一天:
SELECT DATE_TRUNC('month', current_date());
使用 DATEADD 和 DATE_TRUNC 函数获取当前月份的最后一天作为 DATE 值:
SELECT DATEADD('day',-1, DATE_TRUNC('month', DATEADD(day,31,DATE_TRUNC('month',current_date()) ) ) );
替代选项。在下面的示例中, DATE_TRUNC 检索当前月份的开始,加上一个月以检索下一个月的开始,然后减去 1 天以确定当前月份的最后一天。
SELECT DATEADD('day', -1,
DATEADD('month', 1,
DATE_TRUNC('month', current_date())));
获取上个月的最后一天作为 DATE 值:
SELECT DATEADD(day, -1, DATE_TRUNC('month',current_date()) );
按名称获取一年中的当前月份:
-- Output short English names, e.g. "Jan", "Dec", etc.
SELECT TO_VARCHAR(current_date(), 'Mon');
-- Output arbitrary, explicitly-provided month names
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');
获取本周星期一的日期:
SELECT DATEADD('day', (EXTRACT('dayofweek_iso', current_date()) * -1) +1 , current_date() );
获取本周星期五的日期:
SELECT DATEADD('day', (5 - EXTRACT('dayofweek_iso', current_date()) ) , current_date() );
使用 DATE_PART 函数获取当前月份第一个星期一的日期:
SELECT DATEADD(
day,
MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', current_date()) ), 7),
DATE_TRUNC('month', current_date()));
备注
在上面的查询中, 7 + 1
中的 1
值转换为星期一。要检索第一个星期二、星期三等的日期,请分别用 2
、3
等替换,直到 7
替换 Sunday
。
获取当前年份的第一天作为 DATE 值:
SELECT DATE_TRUNC('year', current_date());
获取当前年份的最后一天作为 DATE 值:
SELECT DATEADD('day', -1,
DATEADD('year', 1,
DATE_TRUNC('year', current_date())));
获取上一年的最后一天作为 DATE 值:
SELECT DATEADD('day', -1, DATE_TRUNC('year',current_date()) );
获取当前季度的第一天作为 DATE 值:
SELECT DATE_TRUNC('quarter',current_date());
获取当前季度的最后一天作为 DATE 值:
SELECT DATEADD('day', -1,
DATEADD('month', 3,
DATE_TRUNC('quarter', current_date())));
获取当天午夜的日期和时间戳:
SELECT DATE_TRUNC('day', current_timestamp());
+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700 |
+----------------------------------------+
递增日期和时间值¶
在当前日期的基础上增加两年:
SELECT DATEADD(year, 2, current_date());
在当前日期的基础上增加两天:
SELECT DATEADD(day,2,current_date());
在当前日期和时间的基础上增加两个小时:
SELECT DATEADD(hour,2,current_timestamp());
在当前日期和时间的基础上增加两分钟:
SELECT DATEADD(minute,2,current_timestamp());
在当前日期和时间的基础上增加两秒钟:
SELECT DATEADD(second,2,current_timestamp());
将有效的字符串转换为日期、时间或时间戳¶
在大多数用例中,Snowflake 可以正确处理格式化为字符串的日期和时间戳值。在某些情况下,例如基于字符串的比较,或者当结果依赖于与会话参数中设置的时间戳格式不同的时候,我们建议显式地将值转换为所需的格式,以避免意外的结果。
例如,在没有显式强制类型转换的情况下,比较字符串值会生成基于字符串的结果:
-- Note the column data type is string
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');
-- Comparison without explicit casting
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';
+------------+
| TIMESTAMP1 |
|------------|
+------------+
-- Comparison with explicit casting to date
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::date;
+---------------------------------+
| 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-2016 00:00:00','dd-mon-yyyy hh:mi:ss') );
+--------------------------------------------------------------------------------+
| DATEADD('DAY',5,TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') ) |
|--------------------------------------------------------------------------------|
| Thu, 17 Jan 2016 00:00:00 -0800 |
+--------------------------------------------------------------------------------+
使用 DATEDIFF 函数计算当前日期与字符串中表示的日期之间的天数差:
-- Using the TO_TIMESTAMP function:
SELECT DATEDIFF('day', TO_TIMESTAMP ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss') , current_date() );
+-------------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') , CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------------|
| 240 |
+-------------------------------------------------------------------------------------------------+
-- Using the TO_DATE function:
SELECT DATEDIFF('day', TO_DATE ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss'), current_date() );
+-------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_DATE ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS'), CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------|
| 240 |
+-------------------------------------------------------------------------------------------+
在指定日期上增加一天:
SELECT TO_DATE('2019-01-15') + 1;
+---------------------------+
| TO_DATE('2019-01-15') + 1 |
|---------------------------|
| 2019-01-16 |
+---------------------------+
从当前日期(例如 2019 年 1 月 21 日)减去 9 天:
SELECT CURRENT_DATE() - 9;
+--------------------+
| CURRENT_DATE() - 9 |
|--------------------|
| 2019-01-12 |
+--------------------+
计算日期或时间之间的差异¶
计算当前日期与三年内日期之间的差值:
SELECT DATEDIFF(year, current_date(),
DATEADD(year, 3, current_date() ) );
计算当前日期与三个月后的日期之间的差值:
SELECT DATEDIFF(month, current_date(),
DATEADD(month, 3, current_date()) );
计算当前日期与三天后的日期之间的差值:
SELECT DATEDIFF(day, current_date(),
DATEADD(day, 3, current_date()) );
计算当前时间与三个小时后的时间的差值:
SELECT DATEDIFF(hour, current_timestamp(),
DATEADD(hour, 3, current_timestamp()) );
计算当前时间与三分钟后的时间的差值:
SELECT DATEDIFF(minute, current_timestamp(),
DATEADD(minute, 3, current_timestamp()) );
计算当前时间与三秒后的时间的差值:
SELECT DATEDIFF(second, current_timestamp(),
DATEADD(second, 3, current_timestamp()) );
创建年历视图¶
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;
+-----+------------+-----------------+-----------+---------+
| 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 |
+-----+------------+-----------------+-----------+---------+