# 日期、时间函数及运算符 #### 日期时间运算符 | **运算符** | **示例** | **结果** | |:---|:---|:---| | + | date '2012-08-08' + interval '2' day | 2012-08-10 | | + | time '01:00' + interval '3' hour | 04:00:00.000 | | + | timestamp '2012-08-08 01:00' + interval '29' hour | 2012-08-09 06:00:00.000 | | + | timestamp '2012-10-31 01:00' + interval '1' month | 2012-11-30 01:00:00.000 | | + | interval '2' day + interval '3' hour | 2 03:00:00.000 | | + | interval '3' year + interval '5' month | 3-5 | | - | date '2012-08-08' - interval '2' day | 2012-08-06 | | - | time '01:00' - interval '3' hour | 22:00:00.000 | | - | timestamp '2012-08-08 01:00' - interval '29' hour | 2012-08-06 20:00:00.000 | | - | timestamp '2012-10-31 01:00' - interval '1' month | 2012-09-30 01:00:00.000 | | - | interval '2' day - interval '3' hour | 1 21:00:00.000 | | - | interval '3' year - interval '5' month | 2-7 | #### 时区转换 运算符：AT TIME ZONE，用于设置一个时间戳的时区。 ``` SELECT timestamp '2012-10-31 01:00 UTC';-- 2012-10-31 01:00:00.000 UTC SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'Asia/Singapore'; -- 2012-10-30 09:00:00.000 Asia/Singapore ``` #### 日期时间函数 - current_date -\> date 返回当前日期(utc时区) ``` select current_date; -- 2020-07-25 ``` - current_time -\> time with time zone 返回当前时间(utc时区) ``` select current_time;-- 16:58:48.601+08:00 ``` - current_timestamp -\> timestamp with time zone 返回当前时间戳(当前时区) ``` select current_timestamp; -- 2020-07-25 11:50:27.350 Asia/Singapore ``` - current_timezone() → varchar 返回当前时区 ``` select current_timezone();-- Asia/Singapore ``` - date(x) → date 将日期字面量转换成日期类型的变量 ``` select date('2020-07-25');-- 2020-07-25 ``` - from_iso8601_timestamp(string) → timestamp with time zone 将ISO 8601格式的时戳字面量转换成带时区的时戳变量 ``` SELECT from_iso8601_timestamp('2020-05-11');-- 2020-05-11 00:00:00.000 Asia/Singapore SELECT from_iso8601_timestamp('2020-05-11T11:15:05'); -- 2020-05-11 11:15:05.000 Asia/Singapore SELECT from_iso8601_timestamp('2020-05-11T11:15:05.055+01:00');-- 2020-05-11 11:15:05.055 +01:00 ``` - from_iso8601_date(string) → date 将ISO 8601格式的日期字面量转换成日期类型的变量 ``` SELECT from_iso8601_date('2020-05-11');-- 2020-05-11 SELECT from_iso8601_date('2020-W10');-- 2020-03-02 SELECT from_iso8601_date('2020-123');-- 2020-05-02 ``` - from_unixtime(unixtime) → timestamp with time zone 将UNIX时戳转换为时间戳变量（当前时区） ``` Select FROM_UNIXTIME(1.595658735E9); -- 2020-07-25 14:32:15.000 Asia/Singapore Select FROM_UNIXTIME(875996580); --1997-10-05 04:23:00.000 Asia/Singapore ``` - from_unixtime(unixtime, string) → timestamp with time zone 将UNIX时戳转换成时戳变量，可以带时区选项 ``` select from_unixtime(1.595658735E9, 'Asia/Singapore');-- 2020-07-25 14:32:15.000 Asia/Singapore ``` - from_unixtime(unixtime, hours, minutes) → timestamp with time zone 将UNIX时戳转换成带时区的时戳变量，hours和minutes表示时区偏移量 ``` select from_unixtime(1.595658735E9, 8, 30);-- 2020-07-25 14:32:15.000 +08:30 ``` - localtime -\> time 获取当前时间 ``` select localtime;-- 14:16:13.096 ``` - localtimestamp -\> timestamp 获取当前时间戳 ``` select localtimestamp;-- 2020-07-25 14:17:00.567 ``` - months_between(date1, date2) -\> double 返回date1和date2之间的月数，如果date1比date2晚，结果就是正数，那么结果就是负数；如果两个日期的日数相同，那么结果就是整数，否则按照每月31天以及时分秒的差异来计算小数部分。date1和date2的类型可以是date，timestamp，也可以是"yyyy-MM-dd"或"yyyy-MM-dd HH:mm:ss"格式的字符串 ``` select months_between('2020-02-28 10:30:00', '2021-10-30');-- -20.05040323 select months_between('2021-01-30', '2020-10-30'); -- 3.0 ``` - now() → timestamp with time zone 获取当前时间，current_timestamp的别名 ``` select now();-- 2020-07-25 14:39:39.842 Asia/Singapore ``` - unix_timestamp() 获取当前unix时间戳 ``` select unix_timestamp(); -- 1600930503 ``` - to_iso8601(x) → varchar 将x转换成ISO8601格式的字符串。这里x可以是DATE、TIMESTAMP \[with time zone\]这几个类型 ``` select to_iso8601(date '2020-07-25'); -- 2020-07-25 select to_iso8601(timestamp '2020-07-25 15:22:15.214'); -- 2020-07-25T15:22:15.214 ``` - to_milliseconds(interval) → bigint 获取当前距当天零时已经过去的毫秒数 ``` select to_milliseconds(interval '8' day to second);-- 691200000 ``` - to_unixtime(timestamp) → double 将时间戳转换成UNIX时间 ``` select to_unixtime(cast('2020-07-25 14:32:15.147' as timestamp));-- 1.595658735147E9 ``` - trunc(string date, string format) →string 按照format格式去截取日期值，支持的格式有：MONTH/MON/MM，YEAR/YYYY/YY, QUARTER/Q ``` select trunc(date '2020-07-08','yy');-- 2020-01-01 select trunc(date '2020-07-08','MM');-- 2020-07-01 ``` ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) 使用下列SQL标准函数时，兼容使用圆括号的方式： - current_date - current_time - current_timestamp - localtime - Localtimestamp 如：select current_date(); #### **截取函数** 类似于保留几位小数的操作，函数date_trunc支持如下单位： | 单位 | 截取后的值 | |:---|:---| | second | 2001-08-22 03:04:05.000 | | minute | 2001-08-22 03:04:00.000 | | hour | 2001-08-22 03:00:00.000 | | day | 2001-08-22 00:00:00.000 | | week | 2001-08-20 00:00:00.000 | | month | 2001-08-01 00:00:00.000 | | quarter | 2001-07-01 00:00:00.000 | | year | 2001-01-01 00:00:00.000 | 上面的例子使用时间戳2001-08-22 03:04:05.321作为输入。 date_trunc(unit, x) → \[same as input\] 返回x截取到单位unit之后的值。 ``` select date_trunc('hour', timestamp '2001-08-22 03:04:05.321'); -- 2001-08-22 03:00:00.000 ``` #### 间隔函数 本章中的函数支持如下所列的间隔单位： | 单位 | 描述 | |:---|:---| | second | Seconds | | minute | Minutes | | hour | Hours | | day | Days | | week | Weeks | | month | Months | | quarter | Quarters of a year | | year | Years | - date_add(unit, value, timestamp) → \[same as input\] 在timestamp的基础上加上value个unit。如果想要执行相减的操作，可以通过将value赋值为负数来完成。 ``` SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00');-- 2020-03-01 00:01:26 SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00');-- 2020-03-01 09:00:00 SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC');-- 2020-02-29 00:00:00 UTC ``` - date_diff(unit, timestamp1, timestamp2) → bigint 返回timestamp2 - timestamp1之后的值，该值的表示单位是unit。 unit的值是字符串。例如：'day'、'week'、'year' ``` SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00');-- 86400 SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC');-- 24 SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02');-- 1 SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400 SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400123 ``` - adddate(date, bigint)→ \[same as input\] 描述：日期加法。输入的类型可以是date或timestamp，表示对日期做加减，当做减法时，bigint对应值为负。 ``` select ADDDATE(timestamp '2020-07-04 15:22:15.124',-5);-- 2020-06-29 15:22:15.124 select ADDDATE(date '2020-07-24',5); -- 2020-07-29 ``` #### 持续时间函数 持续时间可以使用以下单位： | 单位 | 描述 | |:---|:---| | ns | 纳秒 | | us | 微秒 | | ms | 毫秒 | | s | 秒 | | m | 分钟 | | h | 小时 | | d | 天 | parse_duration(string) → interval ``` SELECT parse_duration('42.8ms'); -- 0 00:00:00.043 SELECT parse_duration('3.81 d'); -- 3 19:26:24.000 SELECT parse_duration('5m'); -- 0 00:05:00.000 ``` #### MySQL日期函数 在这一章节使用与MySQL date_parse和str_to_date方法兼容的格式化字符串。 - date_format(timestamp, format) → varchar 使用format格式化timestamp。 ``` select date_format(timestamp '2020-07-22 15:00:15', '%Y/%m/%d');-- 2020/07/22 ``` - date_parse(string, format) → timestamp 按format格式解析日期字面量。 ``` select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 ``` 下面的表格是基于MySQL手册列出的，描述了各种格式化描述符： | 格式化描述符 | 描述 | |:---|:---| | %a | 对应的星期几（Sun .. Sat） | | %b | 对应的月份（Jan .. Dec） | | %c | 对应的月份（1 .. 12） | | %D | 对应该月的第几天（0th, 1st, 2nd, 3rd, ...） | | %d | 对应该月的第几天，数字（01 .. 31）（两位，前面会补0） | | %e | 对应该月的第几天，数字（1 .. 31） | | %f | 小数以下的秒（6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999） | | %H | 小时（00 .. 23） | | %h | 小时（01 .. 12） | | %I | 小时（01 .. 12） | | %i | 分钟，数字（00 .. 59） | | %j | 一年的第几天（001 .. 366） | | %k | 小时（0 .. 23） | | %l | 小时（1 .. 12） | | %M | 月份名称（January .. December） | | %m | 月份，数字（01 .. 12） | | %p | AM or PM | | %r | 时间，12小时制（hh:mm:ss followed by AM or PM） | | %S | 秒（00 .. 59） | | %s | 秒（00 .. 59） | | %T | 时间，24小时制（hh:mm:ss） | | %U | 周（00 .. 53）， 星期天是一周的第一天 | | %u | 周（00 .. 53）， 星期一是一周的第一天 | | %V | 周（01 .. 53），星期天是一周的第一天，与%X配合使用 | | %v | 星期（01 .. 53）， 第一条为星期一，与%X配合使用 | | %W | 周几（Sunday .. Saturday） | | %w | 本周的第几天（0 .. 6），星期天是一周的第一天 | | %X | 年份，数字，4位，第一天为星期日 | | %x | 年份，数字，4位，第一天为星期一 | | %Y | 年份，数字，4位 | | %y | 年份，数字，2位，表示年份范围为\[1970, 2069\] | | %% | 表示字符'%' | 示例： ``` select date_format(timestamp '2020-07-25 15:04:00.124','一年的第%j天，%m月的第%d天，%p %T %W'); _col0 --------------------------------------------------- 一年的第207天，07月的第25天，PM 15:04:00 Saturday (1 row) ``` ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) 这些格式化描述符现在还不支持：%D、%U、%u、%V、%w、%X。 - date_format(timestamp, format) → varchar 使用format格式化timestamp - date_parse(string, format) → timestamp 解析时间戳字符串 ``` select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 ``` #### Java日期函数 在这一章节中使用的格式化字符串都是与Java的[SimpleDateFormat](https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html)样式兼容的。 - format_datetime(timestamp, format) → varchar 使用format格式化timestamp - parse_datetime(string, format) → timestamp with time zone 使用指定的格式，将字符串格式化为timestamp with time zone ``` select parse_datetime('1960/01/22 03:04', 'yyyy/MM/dd HH:mm'); _col0 --------------------------------------- 1960-01-22 03:04:00.000 Asia/Shanghai (1 row) ``` #### 常用提取函数 | 域 | 描述 | |:---|:---| | YEAR | year() | | QUARTER | quarter() | | MONTH | month() | | WEEK | week() | | DAY | day() | | DAY_OF_MONTH | day_of_month() | | DAY_OF_WEEK | day_of_week() | | DOW | day_of_week() | | DAY_OF_YEAR | day_of_year() | | DOY | day_of_year() | | YEAR_OF_WEEK | year_of_week() | | YOW | year_of_week() | | HOUR | hour() | | MINUTE | minute() | | SECOND | second() | | TIMEZONE_HOUR | timezone_hour() | | TIMEZONE_MINUTE | timezone_minute() | 例如： ``` select second(timestamp '2020-02-12 15:32:33.215');-- 33 select timezone_hour(timestamp '2020-02-12 15:32:33.215');-- 8 ``` - MONTHNAME(date) 描述：获取月份名称。 ``` SELECT monthname(timestamp '2019-09-09 12:12:12.000');-- SEPTEMBER SELECT monthname(date '2019-07-09');--JULY ``` - extract(field FROM x) → bigint 描述：从x中返回域，对应的域字段，参照本篇的表格。 ``` select extract(YOW FROM timestamp '2020-02-12 15:32:33.215');-- 2020 select extract(SECOND FROM timestamp '2020-02-12 15:32:33.215');-- 33 select extract(DOY FROM timestamp '2020-02-12 15:32:33.215');--43 ``` | 函数 | 示例 | 描述 | |:---|:---|:---| | SECONDS_ADD(TIMESTAMP date, INT seconds) | SELECT seconds_add(timestamp '2019-09-09 12:12:12.000', 10); | 给时间以秒为单位进行加法 | | SECONDS_SUB(TIMESTAMP date, INT seconds) | SELECT seconds_sub(timestamp '2019-09-09 12:12:12.000', 10); | 给时间以秒为单位进行减法 | | MINUTES_ADD(TIMESTAMP date, INT minutes) | SELECT MINUTES_ADD(timestamp '2019-09-09 12:12:12.000', 10); | 给时间以分钟为单位进行加法 | | MINUTES_SUB(TIMESTAMP date, INT minutes) | SELECT MINUTES_SUB(timestamp '2019-09-09 12:12:12.000', 10); | 给时间以分钟为单位进行减法 | | HOURS_ADD(TIMESTAMP date, INT hours) | SELECT HOURS_ADD(timestamp '2019-09-09 12:12:12.000', 1); | 给时间以小时为单位进行加法 | | HOURS_SUB(TIMESTAMP date, INT hours) | SELECT HOURS_SUB(timestamp '2019-09-09 12:12:12.000', 1); | 给时间以小时为单位进行减法 | - last_day(timestamp) -\> date 描述：根据指定的时间戳返回每个月的最后一天。 ``` SELECT last_day(timestamp '2019-09-09 12:12:12.000');-- 2019-09-30 SELECT last_day(date '2019-07-09');--2019-07-31 ``` - add_months(timestamp) -\> \[same as input\] 描述：通过将指定的月份增加指定的日期来返回正确的日期。 ``` SELECT add_months(timestamp'2019-09-09 00:00:00.000', 11);-- 2020-08-09 00:00:00.000 ``` - next_day() (timestamp, string) -\> date 描述：根据指定日期返回指定周几的下一天日期。 ``` SELECT next_day(timestamp'2019-09-09 00:00:00.000', 'monday');-- 2019-09-16 00:00:00.000 SELECT next_day(date'2019-09-09', 'monday');-- 2019-09-16 ``` - numtoday(integer) -\> BIGINT 描述：将传递的整数值转换为day类型，例如BIGINT类型。 ``` SELECT numtoday(2);-- 2 ```