日期和时间函数_系统函数_分析型数据库MySQL版_阿里云技术文档

ADDDATE/DATE_ADD：返回添加指定时间后的日期。
ADDTIME：返回添加指定时间后的时间。
CONVERT_TZ：转换时区，从from_tz转到to_tz给出的时区，并返回结果值。
CURDATE：返回当前日期。
CURTIME：返回当前时间。
DATE：返回日期或日期时间表达式中的日期。
DATE_FORMAT：按照format指定的格式，将日期时间格式化成字符串。
SUBDATE/DATE_SUB：返回date减去指定INTERVAL间隔后的日期。
DATEDIFF：返回expr1减去expr2后的天数。
DAY/DAYOFMONTH：返回date中的日，取值范围[1,31]。
DAYNAME：返回日期对应的工作日的名称，例如星期一为Monday。
DAYOFWEEK：返回日期对应的工作日索引值。
DAYOFYEAR：返回指定日期是当年的哪一天。
EXTRACT：返回日期或时间的单独部分，例如年、月、日、小时、分钟等。
FROM_DAYS：根据指定的天数N，返回对应的DATE值。
FROM_UNIXTIME：返回unixtime时间戳。
HOUR：返回时间中的小时。
LAST_DAY：返回日期或者日期时间中对应月份的最后一天。
LOCALTIME/LOCALTIMESTAMP/NOW：返回当前时间戳。
MAKEDATE：按照参数year和dayofyear，返回一个日期。
MAKETIME：按照参数hour、minute和second，返回一个时间。
MINUTE：返回时间中的分钟。
MONTH：返回日期中的月份。
MONTHNAME：返回日期中月份的全名。
PERIOD_ADD：将日期格式的参数P增加N个月。
PERIOD_DIFF：返回P1和P2之间相差的月数。
QUARTER：返回日期在一年中的季度。
SEC_TO_TIME：将seconds转换为时间。
SECOND：返回时间中的秒。
STR_TO_DATE：按照指定日期或时间显示格式，将字符串转换为日期或日期时间类型。
SUBTIME：返回expr1减去expr2后的时间。
SYSDATE：获取系统时间。
TIME：以字符串形式返回expr中的时间。
TIME_FORMAT：按照format指定的格式，以字符串形式显示时间。
TIME_TO_SEC：返回time转换为秒的结果。
TIMEDIFF：返回expr1减去expr2后的时间。
TIMESTAMP：返回expr表示的日期或日期时间。
TIMESTAMPADD：将interval添加到日期或日期时间表达式datetime_expr中。
TIMESTAMPDIFF：返回日期或日期时间表达式datetime_expr1减去datetime_expr2后的结果。
TO_DAYS：根据给定日期date，返回自0年开始的天数。
TO_SECONDS：根据给定的expr，返回自0年开始的秒数。
UNIX_TIMESTAMP：返回自’1970-01-01 00:00:00’ UTC以来秒数的Unix时间戳。
UTC_DATE：返回UTC日期。
UTC_TIME：返回UTC时间。
UTC_TIMESTAMP：返回UTC时间戳。
UTC_DATE：返回UTC日期。
WEEK：返回日期对应的周数。
WEEKDAY：返回日期对应的工作日。
WEEKOFYEAR：返回日期对应的日历周。
YEAR：返回日期中的年份。
YEARWEEK：返回日期的年份和星期。

ADDDATE/DATE_ADD

ADDDATE(date,INTERVAL expr unit)
ADDDATE(expr,days)

命令说明：返回添加指定时间后的日期。
- unit可取值为：second、minute、hour、day、month、 year、minute_second、hour_second、hour_minute、day_second、 day_minute、day_hour、year_month。unit默认值为day。
- days、expr：系统将返回expr加上days之后的结果。

参数类型：

  adddate(date, INTERVAL expr unit)
  adddate(timestamp, INTERVAL expr unit)
  adddate(datetime, INTERVAL expr unit)
  adddate(varchar, INTERVAL expr unit)
  adddate(date, varchar)
  adddate(date, bigint)
  adddate(datetime, bigint)
  adddate(datetime, varchar)
  adddate(timestamp, varchar)
  adddate(timestamp, bigint)
  adddate(varchar, bigint)
  adddate(varchar, varchar)

返回值类型：DATE。

示例：

  select adddate(date '2001-1-22',interval '3' day);
  +----------------------------------------------+
  | adddate(DATE '2001-1-22', INTERVAL  '3' DAY) |
  +----------------------------------------------+
  | 2001-01-25                                   |

  select adddate(timestamp '2001-1-22',interval '3' day);
  +---------------------------------------------------+
  | adddate(TIMESTAMP '2001-1-22', INTERVAL  '3' DAY) |
  +---------------------------------------------------+
  | 2001-01-25 00:00:00                               |

  select adddate(datetime '2001-1-22',interval '3' day);
  +--------------------------------------------------+
  | adddate(DATETIME '2001-1-22', INTERVAL  '3' DAY) |
  +--------------------------------------------------+
  | 2001-01-25 00:00:00                              |

  select adddate('2001-1-22',interval '3' day);
  +-----------------------------------------+
  | adddate('2001-1-22', INTERVAL  '3' DAY) |
  +-----------------------------------------+
  | 2001-01-25                              |

  select adddate(datetime '2001-1-22',interval '3' second);
  +-----------------------------------------------------+
  | adddate(DATETIME '2001-1-22', INTERVAL  '3' SECOND) |
  +-----------------------------------------------------+
  |                                 2001-01-22 00:00:03 |

  select adddate(datetime '2001-1-22',interval '3' minute);
  +-----------------------------------------------------+
  | adddate(DATETIME '2001-1-22', INTERVAL  '3' MINUTE) |
  +-----------------------------------------------------+
  |                                 2001-01-22 00:03:00 |

  select adddate(datetime '2001-1-22',interval '3' hour);
  +---------------------------------------------------+
  | adddate(DATETIME '2001-1-22', INTERVAL  '3' HOUR) |
  +---------------------------------------------------+
  |                               2001-01-22 03:00:00 |

  select adddate(datetime '2001-1-22',interval '3' month);
  +----------------------------------------------------+
  | adddate(DATETIME '2001-1-22', INTERVAL  '3' MONTH) |
  +----------------------------------------------------+
  |                                2001-04-22 00:00:00 |

  select adddate(datetime '2001-1-22',interval '3' year);
  +---------------------------------------------------+
  | adddate(DATETIME '2001-1-22', INTERVAL  '3' YEAR) |
  +---------------------------------------------------+
  |                               2004-01-22 00:00:00 |

  select adddate(datetime '2001-1-22',interval '3' minute_second) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-22 00:03:00 |

  select adddate(datetime '2001-1-22',interval '3' hour_second) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-22 03:00:00 |

  select adddate(datetime '2001-1-22',interval '3' hour_minute) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-22 03:00:00 |

  select adddate(datetime '2001-1-22',interval '3' day_second) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-25 00:00:00 |

  adddate(datetime '2001-1-22',interval '3' day_minute) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-25 00:00:00 |

  select adddate(datetime '2001-1-22',interval '3' day_hour) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-25 00:00:00 |

  select adddate(datetime '2001-1-22 12:32:1',interval '4' year_month) as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2005-01-22 12:32:01 |

  select adddate('2001-1-22','3');
  +---------------------------+
  | adddate('2001-1-22', '3') |
  +---------------------------+
  | 2001-01-25                |

  select adddate('2001-1-22',3);
  +-------------------------+
  | adddate('2001-1-22', 3) |
  +-------------------------+
  | 2001-01-25              |

  select adddate(datetime '2001-1-22 12:12:32',3);
  +-------------------------------------------+
  | adddate(DATETIME '2001-1-22 12:12:32', 3) |
  +-------------------------------------------+
  |                       2001-01-25 12:12:32 |

  select adddate(datetime '2001-1-22 12:12:32','3');
  +---------------------------------------------+
  | adddate(DATETIME '2001-1-22 12:12:32', '3') |
  +---------------------------------------------+
  |                         2001-01-25 12:12:32 |

  select adddate(timestamp '2001-1-22 12:12:32','3');
  +----------------------------------------------+
  | adddate(TIMESTAMP '2001-1-22 12:12:32', '3') |
  +----------------------------------------------+
  |                          2001-01-25 12:12:32 |

  select adddate(timestamp '2001-1-22 12:12:32',3);
  +--------------------------------------------+
  | adddate(TIMESTAMP '2001-1-22 12:12:32', 3) |
  +--------------------------------------------+
  |                        2001-01-25 12:12:32 |

  select adddate('2001-1-22 12:12:32',3);
  +----------------------------------+
  | adddate('2001-1-22 12:12:32', 3) |
  +----------------------------------+
  | 2001-01-25 12:12:32              |

  select adddate('2001-1-22 12:12:32','3');
  +------------------------------------+
  | adddate('2001-1-22 12:12:32', '3') |
  +------------------------------------+
  | 2001-01-25 12:12:32                |

ADDTIME

ADDTIME(expr1,expr2)

命令说明：返回添加指定时间后的时间，即返回expr1增加expr2后的结果。

参数类型：

  addtime(date,varchar)
  addtime(time,varchar)
  addtime(datetime,varchar)
  addtime(timestamp,varchar)
  addtime(varchar,varchar)

返回值类型：VARCHAR。

示例：

  select addtime(date '1998-01-01','01:01:01');
  +----------------------------------------+
  | addtime(DATE '1998-01-01', '01:01:01') |
  +----------------------------------------+
  | 1998-01-01 01:01:01                    |

  select addtime(time '00:00:00','01:01:01');
  +--------------------------------------+
  | addtime(TIME '00:00:00', '01:01:01') |
  +--------------------------------------+
  | 01:01:01                             |

  select addtime(datetime '2001-1-22 00:00:00','01:01:01');
  +----------------------------------------------------+
  | addtime(DATETIME '2001-1-22 00:00:00', '01:01:01') |
  +----------------------------------------------------+
  | 2001-01-22 01:01:01                                |

  select addtime(timestamp '2001-1-22 00:00:00','01:01:01');
  +-----------------------------------------------------+
  | addtime(TIMESTAMP '2001-1-22 00:00:00', '01:01:01') |
  +-----------------------------------------------------+
  | 2001-01-22 01:01:01                                 |

  select addtime('2001-1-22 00:00:00','01:01:01');
  +-------------------------------------------+
  | addtime('2001-1-22 00:00:00', '01:01:01') |
  +-------------------------------------------+
  | 2001-01-22 01:01:01                       |

CONVERT_TZ

CONVERT_TZ(dt,from_tz,to_tz)

命令说明：转换dt，从from_tz转到to_tz给出的时区，并返回结果。
参数类型：
```
  convert_tz(varchar, varchar, varchar)
```
返回值类型：DATETIME。

示例：

  select convert_tz('2004-01-01 12:00:00','+00:00','+10:00');
  +-------------------------------------------------------+
  | convert_tz('2004-01-01 12:00:00', '+00:00', '+10:00') |
  +-------------------------------------------------------+
  |                                   2004-01-01 22:00:00 |

  select convert_tz('2004-01-01 12:00:00','GMT','MET');
  +-------------------------------------------------+
  | convert_tz('2004-01-01 12:00:00', 'GMT', 'MET') |
  +-------------------------------------------------+
  |                             2004-01-01 13:00:00 |

CURDATE

CURDATE()

命令说明：返回当前日期。
返回值类型：DATE。

示例：

  select curdate;
  +------------+
  | curdate()  |
  +------------+
  | 2019-05-25 |

CURTIME

CURTIME()

命令说明：返回当前时间。
返回值类型：TIME。

示例：

  select curtime();
  +--------------+
  | curtime()    |
  +--------------+
  | 14:39:22.109 |

DATE

DATE(expr)

命令说明：返回日期或日期时间表达式中的日期。

参数类型：

  date(timestamp)
  date(datetime)
  date(varchar)

返回值类型：DATE。

示例：

  select date(timestamp '2003-12-31 01:02:03');
  +---------------------------------------+
  | date(TIMESTAMP '2003-12-31 01:02:03') |
  +---------------------------------------+
  | 2003-12-31                            |

  select date(datetime '2003-12-31 01:02:03');
  +--------------------------------------+
  | date(DATETIME '2003-12-31 01:02:03') |
  +--------------------------------------+
  | 2003-12-31                           |

  select date('2003-12-31 01:02:03');
  +-----------------------------+
  | date('2003-12-31 01:02:03') |
  +-----------------------------+
  | 2003-12-31                  |

DATE_FORMAT

DATE_FORMAT(date,format)

命令说明：按照format指定的格式，将日期时间格式化成字符串。format格式如下所示。

符号	说明
%a	工作日缩写名称（Sun.. Sat）
%b	缩写的月份名称（Jan.. Dec）
%c	月，数字（0.. 12）
%d	每月的某一天，数字（00.. 31）
%e	每月的某一天，数字（0.. 31）
%f	微秒（000000… 999999）
%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	月，数字（00.. 12）
%p	AM或PM
%r	时间，12小时（hh:mm:ss其次是AM或PM）
%S	秒（00… 59）
%s	秒（00… 59）
%T	时间，24小时（hh:mm:ss）
%v	本周是当年的第几周，星期一是一周的第一天，WEEK()模式3; 与%x 使用
%W	工作日名称（Sunday.. Saturday）
%x	本周所属年份，星期一是一周的第一天，四位数; 与%v使用
%Y	年份，数字，四位数
%y	年份，数字，两位数
%%	文字%字符
%x	x，对于上面未列出的任何x

参数类型：

  date_format(timestamp, varchar)
  date_format(varchar, varchar) 
  date_format(datetime, varchar)
  date_format(date, varchar)

返回值类型：VARCHAR。

示例：

  select date_format(timestamp '2019-05-27 13:23:00', '%W %M %Y')as result;
  +-----------------+
  | result          |
  +-----------------+
  | Monday May 2019 |

  select date_format('2019-05-27 13:23:00', '%W %M %Y')as result;
  +-----------------+
  | result          |
  +-----------------+
  | Monday May 2019 |

  select date_format(datetime '2019-05-27 13:23:00', '%W %M %Y')as result;
  +-----------------+
  | result          |
  +-----------------+
  | Monday May 2019 |

  select date_format(date '2019-05-27', '%W %M %Y')as result;
  +-----------------+
  | result          |
  +-----------------+
  | Monday May 2019 |

SUBDATE/DATE_SUB

DATE_SUB(date,INTERVAL expr unit)

命令说明：返回date减去指定INTERVAL间隔后的日期。

unit可取值为：second、minute、hour、day、month、 year、minute_second、hour_second、hour_minute、day_second、 day_minute、day_hour、year_month。unit默认值为day。

参数类型：

  subdate(date, INTERVAL expr unit)
  subdate(timestamp, INTERVAL expr unit)
  subdate(datetime, INTERVAL expr unit)
  subdate(varchar, INTERVAL expr unit)
  subdate(date, bigint)
  subdate(date, varchar)
  subdate(datetime, bigint)
  subdate(datetime, varchar)
  subdate(timestamp, bigint)
  subdate(timestamp, varchar)
  subdate(varchar, bigint)
  subdate(varchar, varchar)

返回值类型：DATE。

示例：

  select date_sub(date '2001-1-22',interval '3' day);
  +-----------------------------------------------+
  | date_sub(DATE '2001-1-22', INTERVAL  '3' DAY) |
  +-----------------------------------------------+
  | 2001-01-19                                    |

  select date_sub(timestamp '2001-1-22 00:00:00',interval '3' day)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-19 00:00:00 |

  select date_sub(datetime '2001-1-22 00:00:00',interval '3' day)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-19 00:00:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' day);
  +---------------------------------------------------+
  | date_sub('2001-1-22 00:00:00', INTERVAL  '3' DAY) |
  +---------------------------------------------------+
  | 2001-01-19 00:00:00                               |

  select date_sub('2001-1-22 00:00:00',interval '3' second);
  +------------------------------------------------------+
  | date_sub('2001-1-22 00:00:00', INTERVAL  '3' SECOND) |
  +------------------------------------------------------+
  | 2001-01-21 23:59:57                                  |

  select date_sub('2001-1-22 00:00:00',interval '3' minute);
  +------------------------------------------------------+
  | date_sub('2001-1-22 00:00:00', INTERVAL  '3' MINUTE) |
  +------------------------------------------------------+
  | 2001-01-21 23:57:00                                  |

  select date_sub('2001-1-22 00:00:00',interval '3' hour);
  +----------------------------------------------------+
  | date_sub('2001-1-22 00:00:00', INTERVAL  '3' HOUR) |
  +----------------------------------------------------+
  | 2001-01-21 21:00:00                                |

  select date_sub('2001-1-22 00:00:00',interval '3' month);
  +-----------------------------------------------------+
  | date_sub('2001-1-22 00:00:00', INTERVAL  '3' MONTH) |
  +-----------------------------------------------------+
  | 2000-10-22 00:00:00                                 |

  select date_sub('2001-1-22 00:00:00',interval '3' year);
  +----------------------------------------------------+
  | date_sub('2001-1-22 00:00:00', INTERVAL  '3' YEAR) |
  +----------------------------------------------------+
  | 1998-01-22 00:00:00                                |

  select date_sub('2001-1-22 00:00:00',interval '3' minute_second)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-21 23:57:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' hour_second)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-21 21:00:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' hour_minute)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-21 21:00:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' day_second)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-19 00:00:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' day_minute)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-19 00:00:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' day_hour)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2001-01-19 00:00:00 |

  select date_sub('2001-1-22 00:00:00',interval '3' year_month)as result;
  +---------------------+
  | result              |
  +---------------------+
  | 1998-01-22 00:00:00 |

  select date_sub(date '2001-1-22 00:00:00',3);
  +----------------------------------------+
  | date_sub(DATE '2001-1-22 00:00:00', 3) |
  +----------------------------------------+
  | 2001-01-19                             |

  select date_sub(date '2001-1-22 00:00:00','3');
  +------------------------------------------+
  | date_sub(DATE '2001-1-22 00:00:00', '3') |
  +------------------------------------------+
  | 2001-01-19                               |

  select date_sub(datetime '2001-1-22 00:00:00',3);
  +--------------------------------------------+
  | date_sub(DATETIME '2001-1-22 00:00:00', 3) |
  +--------------------------------------------+
  |                        2001-01-19 00:00:00 |

  select date_sub(datetime '2001-1-22 00:00:00','3');
  +----------------------------------------------+
  | date_sub(DATETIME '2001-1-22 00:00:00', '3') |
  +----------------------------------------------+
  |                          2001-01-19 00:00:00 |

  select date_sub(timestamp '2001-1-22 00:00:00',3);
  +---------------------------------------------+
  | date_sub(TIMESTAMP '2001-1-22 00:00:00', 3) |
  +---------------------------------------------+
  |                         2001-01-19 00:00:00 |

  select date_sub(timestamp '2001-1-22 00:00:00','3');
  +-----------------------------------------------+
  | date_sub(TIMESTAMP '2001-1-22 00:00:00', '3') |
  +-----------------------------------------------+
  |                           2001-01-19 00:00:00 |

  select date_sub('2001-1-22 00:00:00',3);
  +-----------------------------------+
  | date_sub('2001-1-22 00:00:00', 3) |
  +-----------------------------------+
  | 2001-01-19 00:00:00               |

  select date_sub('2001-1-22 00:00:00','3');
  +-------------------------------------+
  | date_sub('2001-1-22 00:00:00', '3') |
  +-------------------------------------+
  | 2001-01-19 00:00:00                 |

DATEDIFF

DATEDIFF(expr1,expr2)

命令说明：返回expr1减去expr2后的天数。

参数类型：

  datediff(varchar, varchar) 
  datediff(datetime, varchar)
  datediff(varchar, datetime)
  datediff(datetime, datetime)
  datediff(varchar, timestamp)
  datediff(timestamp, timestamp)
  datediff(timestamp, varchar)
  datediff(date, date)
  datediff(date, varchar)
  datediff(varchar, date)

返回值类型：BIGINT。

示例：

  select datediff('2007-12-31 23:59:59','2007-12-30');
  +-----------------------------------------------+
  | datediff('2007-12-31 23:59:59', '2007-12-30') |
  +-----------------------------------------------+
  |                                             1 |

  select datediff(datetime '2007-12-31 23:59:59','2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff('2007-12-31 23:59:59',datetime '2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff(datetime '2007-12-31 23:59:59',datetime '2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff('2007-12-31 23:59:59',timestamp '2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff(timestamp '2007-12-31 23:59:59',timestamp '2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff(timestamp '2007-12-31 23:59:59','2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff(date '2007-12-31 23:59:59',date '2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff(date '2007-12-31 23:59:59','2007-12-30')as result;
  +--------+
  | result |
  +--------+
  |      1 |

  select datediff('2008-12-31',date '2007-12-30');
  +-------------------------------------------+
  | datediff('2008-12-31', DATE '2007-12-30') |
  +-------------------------------------------+
  |                                       367 |

DAY/DAYOFMONTH

DAY(date)
DAYOFMONTH(date)

命令说明：返回date中的日，取值范围[1,31]。

参数类型：

  dayofmonth(timestamp)
  dayofmonth(datetime)
  dayofmonth(date)
  dayofmonth(time)
  dayofmonth(varchar)

返回值类型：BIGINT。

示例：

  select dayofmonth(timestamp '2007-02-03 12:23:09');
  +---------------------------------------------+
  | dayofmonth(TIMESTAMP '2007-02-03 12:23:09') |
  +---------------------------------------------+
  |                                           3 |

  select dayofmonth(date '2007-02-03');
  +-------------------------------+
  | dayofmonth(DATE '2007-02-03') |
  +-------------------------------+
  |                             3 |

  select dayofmonth(time '17:01:10');
  +-----------------------------+
  | dayofmonth(TIME '17:01:10') |
  +-----------------------------+
  |                          30 |

  select day('2007-02-03');
  +-------------------+
  | day('2007-02-03') |
  +-------------------+
  |                 3 |

  select dayofmonth(datetime '2007-02-03 00:00:00');
  +--------------------------------------------+
  | dayofmonth(DATETIME '2007-02-03 00:00:00') |
  +--------------------------------------------+
  |                                          3 |

DAYNAME

DAYNAME(date)

命令说明：返回日期对应的工作日的名称，例如星期一为Monday 。

参数类型：

  dayname(timestamp)
  dayname(datetime)
  dayname(date)
  dayname(varchar)

返回值类型：VARCHAR。

示例：

  select dayname(timestamp '2007-02-03 00:00:00');
  +------------------------------------------+
  | dayname(TIMESTAMP '2007-02-03 00:00:00') |
  +------------------------------------------+
  | Saturday                                 |

  select dayname(datetime '2007-02-03 00:00:00');
  +-----------------------------------------+
  | dayname(DATETIME '2007-02-03 00:00:00') |
  +-----------------------------------------+
  | Saturday                                |

  select dayname(date '2007-02-04');
  +----------------------------+
  | dayname(DATE '2007-02-04') |
  +----------------------------+
  | Sunday                     |

  select dayname('2007-02-03');
  +-----------------------+
  | dayname('2007-02-03') |
  +-----------------------+
  | Saturday              |

DAYOFWEEK

DAYOFWEEK(date)

命令说明：返回日期对应的工作日索引值，即星期日为1，星期一为2，星期六为7。

参数类型：

  dayofweek(timestamp)
  dayofweek(datetime)
  dayofweek(date)
  dayofweek(varchar)

返回值类型：BIGINT。

示例：

  select dayofweek(timestamp '2007-02-03 00:00:00');
  +--------------------------------------------+
  | dayofweek(TIMESTAMP '2007-02-03 00:00:00') |
  +--------------------------------------------+
  |                                          7 |

  select dayofweek(datetime '2007-02-03 00:00:00');
  +-------------------------------------------+
  | dayofweek(DATETIME '2007-02-03 00:00:00') |
  +-------------------------------------------+
  |                                         7 |

  select dayofweek(date '2007-02-03');
  +------------------------------+
  | dayofweek(DATE '2007-02-03') |
  +------------------------------+
  |                            7 |

  select dayofweek('2007-02-03');
  +-------------------------+
  | dayofweek('2007-02-03') |
  +-------------------------+
  |          7              |

DAYOFYEAR

DAYOFYEAR(date)

命令说明：返回指定日期是当年的哪一天，返回值范围为[1,366]。

参数类型：

  dayofyear(timestamp)
  dayofyear(datetime) 
  dayofyear(date) 
  dayofyear(varchar)

返回值类型：BIGINT。

示例：

  select dayofyear(timestamp '2007-02-03 00:12:12');
  +--------------------------------------------+
  | dayofyear(TIMESTAMP '2007-02-03 00:12:12') |
  +--------------------------------------------+
  |                                         34 |

  select dayofyear(datetime '2007-02-03 00:12:12');
  +-------------------------------------------+
  | dayofyear(DATETIME '2007-02-03 00:12:12') |
  +-------------------------------------------+
  |                                        34 |

  select dayofyear(date '2007-02-03');
  +------------------------------+
  | dayofyear(DATE '2007-02-03') |
  +------------------------------+
  |                           34 |

  select dayofyear('2007-02-03');
  +-------------------------+
  | dayofyear('2007-02-03') |
  +-------------------------+
  |            34           |

EXTRACT

EXTRACT(unit FROM date)

命令说明：返回日期或时间的单独部分，由unit指定，比如年、月、日、小时、分钟等。

unit可取值为：second、minute、hour、day、month、 year、minute_second、hour_second、hour_minute、day_second、 day_minute、day_hour、year_month。
支持抽取的入参时间类型：VARCHAR、TIMESTAMP、DATETIME、TIME。
返回值类型：BIGINT。

示例：

  select extract(second from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  |    34 |

  select extract(minute from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  |    12 |

  select extract(hour from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  |     0 |

  select extract(month from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  |     7 |

  select extract(minute_second from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  |  1234 |

  select extract(hour_second from '2019-07-02 12:12:34');
  +--------+
  | _col0  |
  +--------+
  | 121234 |

  select extract(hour_minute from '2019-07-02 12:12:34');
  +-------+
  | _col0 |
  +-------+
  |  1212 |

  select extract(day_second from '2019-07-02 12:12:34');
  +---------+
  | _col0   |
  +---------+
  | 2121234 |

  select extract(day_hour from '2019-07-02 12:12:34');
  +-------+
  | _col0 |
  +-------+
  |   212 |

  select extract(day from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  |     2 |

  select extract(year_month from '2019-07-02 00:12:34');
  +--------+
  | _col0  |
  +--------+
  | 201907 |

  select extract(day_minute from '2019-07-02 00:12:34');
  +-------+
  | _col0 |
  +-------+
  | 20012 |

  select extract(year from timestamp  '2019-05-30');
  +-------+
  | _col0 |
  +-------+
  |  2019 |

  select extract(year from datetime  '2019-05-30');
  +-------+
  | _col0 |
  +-------+
  |  2019 |

  select extract(year from time '15:23:22');
  +-------+
  | _col0 |
  +-------+
  |  2019 |

FROM_DAYS

FROM_DAYS(N)

命令说明：根据指定的天数N，返回对应的DATE值。

参数类型：

  from_days(varchar) 
  from_days(bigint)

返回值类型：DATE。

示例：

  select from_days(730669);
  +-------------------+
  | from_days(730669) |
  +-------------------+
  | 2000-07-03        |

  select from_days('730669');
  +---------------------+
  | from_days('730669') |
  +---------------------+
  | 2000-07-03          |

FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp[,format])

命令说明：返回unixtime时间戳。

format遵从DATE_FORMAT函数中的format格式。

参数类型：

  from_unixtime(varchar, varchar)
  from_unixtime(varchar)
  from_unixtime(double, varchar)
  from_unixtime(double)

返回值类型：DATETIME。

示例：

  select from_unixtime('1447430881','%Y %M %h:%i:%s %x');
  +--------------------------------------------------+
  | from_unixtime('1447430881', '%Y %M %h:%i:%s %x') |
  +--------------------------------------------------+
  | 2015 November 12:08:01 2015                      |

  select from_unixtime('1447430881');
  +-----------------------------+
  | from_unixtime('1447430881') |
  +-----------------------------+
  |         2015-11-14 00:08:01 |

  select from_unixtime(1447430881);
  +---------------------------+
  | from_unixtime(1447430881) |
  +---------------------------+
  |       2015-11-14 00:08:01 |

  select from_unixtime(1447430881,'%Y %M %h:%i:%s %x');
  +------------------------------------------------+
  | from_unixtime(1447430881, '%Y %M %h:%i:%s %x') |
  +------------------------------------------------+
  | 2015 November 12:08:01 2015                    |

HOUR

HOUR(time)

命令说明：返回时间中的小时。

参数类型：

  hour(timestamp)
  hour(datetime)
  hour(date)
  hour(time)
  hour(varchar)

返回值类型：BIGINT。

示例：

  select hour(timestamp '2019-12-07 10:05:03');
  +---------------------------------------+
  | hour(TIMESTAMP '2019-12-07 10:05:03') |
  +---------------------------------------+
  |                                    10 |

  select hour(datetime '2019-12-07 10:05:03');
  +--------------------------------------+
  | hour(DATETIME '2019-12-07 10:05:03') |
  +--------------------------------------+
  |                                   10 |

  select hour(date '2019-12-07');
  +-------------------------+
  | hour(DATE '2019-12-07') |
  +-------------------------+
  |                       0 |

  select hour(time '10:05:03');
  +-----------------------+
  | hour(TIME '10:05:03') |
  +-----------------------+
  |                    10 |

  select hour('10:05:03');
  +------------------+
  | hour('10:05:03') |
  +------------------+
  |       10         |

LAST_DAY

LAST_DAY(date)

命令说明：返回日期或者日期时间中对应月份的最后一天。

参数类型：

  last_day(varchar)
  last_day(timestamp)
  last_day(datetime)
  last_day(date)

返回值类型：DATE。

示例：

  select last_day('2003-02-05');
  +------------------------+
  | last_day('2003-02-05') |
  +------------------------+
  | 2003-02-28             |

  select last_day(timestamp '2003-02-05 12:12:12');
  +-------------------------------------------+
  | last_day(TIMESTAMP '2003-02-05 12:12:12') |
  +-------------------------------------------+
  | 2003-02-28                                |

  select last_day(datetime '2003-02-05 12:12:12');
  +------------------------------------------+
  | last_day(DATETIME '2003-02-05 12:12:12') |
  +------------------------------------------+
  | 2003-02-28                               |

  select last_day(date '2003-02-05');
  +-----------------------------+
  | last_day(DATE '2003-02-05') |
  +-----------------------------+
  | 2003-02-28                  |

LOCALTIME/LOCALTIMESTAMP/NOW

localtime
localtime()
localtimestamp
localtimestamp()
now()

命令说明：返回当前时间戳。
返回值类型：DATETIME。

示例：

  select now();
  +---------------------+
  | now()              |
  +---------------------+
  | 2019-05-25 00:28:37 |

  select localtime;
  +---------------------+
  | localtime()         |
  +---------------------+
  | 2019-05-28 20:44:25 |

  select localtime();
  +---------------------+
  | localtime()         |
  +---------------------+
  | 2019-05-31 17:37:36 |

  select localtimestamp;
  +---------------------+
  | localtimestamp()    |
  +---------------------+
  | 2019-05-28 20:44:44 |

  select localtimestamp();
  +---------------------+
  | localtimestamp()    |
  +---------------------+
  | 2019-05-31 17:38:13 |

MAKEDATE

MAKEDATE(year,dayofyear)

命令说明：按照参数year和dayofyear，返回一个日期。

参数类型：

  makedate(bigint, bigint)
  makedate(varchar, varchar)

返回值类型：DATE。

示例：

  select makedate(2011,31), makedate(2011,32);
  +--------------------+--------------------+
  | makedate(2011, 31) | makedate(2011, 32) |
  +--------------------+--------------------+
  | 2011-01-31         | 2011-02-01         |

  select makedate('2011','31'), makedate('2011','32');
  +------------------------+------------------------+
  | makedate('2011', '31') | makedate('2011', '32') |
  +------------------------+------------------------+
  | 2011-01-31             | 2011-02-01             |

MAKETIME

MAKETIME(hour,minute,second)

命令说明：按照参数hour、minute和second，返回一个时间。

参数类型：

  maketime(bigint, bigint, bigint)
  maketime(varchar, varchar, varchar)

返回值类型：TIME。

示例：

  select maketime(12,15,30);
  +----------------------+
  | maketime(12, 15, 30) |
  +----------------------+
  | 12:15:30             |

  select maketime('12','15','30');
  +----------------------------+
  | maketime('12', '15', '30') |
  +----------------------------+
  | 12:15:30                   |

MINUTE

MINUTE(time)

命令说明：返回时间中的分钟。

参数类型：

  minute(timestamp)
  minute(datetime)
  minute(date)
  minute(time)
  minute(varchar)

返回值类型：BIGINT。

示例：

  select minute(timestamp '2008-02-03 10:05:03');
  +-----------------------------------------+
  | minute(TIMESTAMP '2008-02-03 10:05:03') |
  +-----------------------------------------+
  |                                       5 |

  select minute(datetime '2008-02-03 10:05:03');
  +----------------------------------------+
  | minute(DATETIME '2008-02-03 10:05:03') |
  +----------------------------------------+
  |                                      5 |

  select minute(date '2008-02-03');
  +---------------------------+
  | minute(DATE '2008-02-03') |
  +---------------------------+
  |                         0 |

  select minute(time '12:12:12');
  +-------------------------+
  | minute(TIME '12:12:12') |
  +-------------------------+
  |                      12 |

  select minute('2008-02-03 10:05:03');
  +-------------------------------+
  | minute('2008-02-03 10:05:03') |
  +-------------------------------+
  |            5                  |

MONTH

MONTH(date)

命令说明：返回日期中的月份。

参数类型：

  month(timestamp)
  month(datetime)
  month(date)
  month(time)
  month(varchar)

返回值类型：BIGINT。

示例：

  select month(timestamp '2008-02-03 00:00:00');
  +----------------------------------------+
  | month(TIMESTAMP '2008-02-03 00:00:00') |
  +----------------------------------------+
  |                                      2 |

  select month(datetime '2008-02-03 00:00:00');
  +---------------------------------------+
  | month(DATETIME '2008-02-03 00:00:00') |
  +---------------------------------------+
  |                                     2 |

  select month(date '2008-02-03');
  +--------------------------+
  | month(DATE '2008-02-03') |
  +--------------------------+
  |                        2 |

MONTH函数也可以返回SQL执行时的月份，例如以下SQL是2019年5月执行的，返回结果为5。

  select month(time '12:12:12');
  +------------------------+
  | month(TIME '12:12:12') |
  +------------------------+
  |                      5 |

  select month('2008-02-03');
  +---------------------+
  | month('2008-02-03') |
  +---------------------+
  |                   2 |

MONTHNAME

MONTHNAME(date)

命令说明：返回日期中月份的全名。

参数类型：

  monthname(timestamp)
  monthname(datetime)
  monthname(date)
  monthname(varchar)

返回值类型：VARCHAR。

示例：

  select monthname(timestamp '2008-02-03 00:00:00');
  +--------------------------------------------+
  | monthname(TIMESTAMP '2008-02-03 00:00:00') |
  +--------------------------------------------+
  | February                                   |

  select monthname(datetime '2008-02-03 00:00:00');
  +-------------------------------------------+
  | monthname(DATETIME '2008-02-03 00:00:00') |
  +-------------------------------------------+
  | February                                  |

  select monthname(date '2008-02-03');
  +------------------------------+
  | monthname(DATE '2008-02-03') |
  +------------------------------+
  | February                     |

  select monthname('2008-02-03');
  +-------------------------+
  | monthname('2008-02-03') |
  +-------------------------+
  | February                |

PERIOD_ADD

PERIOD_ADD(P,N)

命令说明：将日期格式的参数P增加N个月。

参数类型：

  period_add(bigint, bigint)
  period_add(varchar, varchar) 
  period_add(varchar, bigint)

返回值类型：BIGINT。

示例：

  select period_add(200801,2);
  +-----------------------+
  | period_add(200801, 2) |
  +-----------------------+
  |                200803 |

  select period_add('200801','2');
  +---------------------------+
  | period_add('200801', '2') |
  +---------------------------+
  |                    200803 |

  select period_add('200801',2);
  +-------------------------+
  | period_add('200801', 2) |
  +-------------------------+
  |                  200803 |

PERIOD_DIFF

PERIOD_DIFF(P1,P2)

命令说明：返回P1和P2之间相差的月数。

参数类型：

  period_diff(bigint, bigint)
  period_diff(varchar, varchar)

返回值类型：BIGINT。

示例：

  select period_diff(200802,200703);
  +-----------------------------+
  | period_diff(200802, 200703) |
  +-----------------------------+
  |                          11 |

  select period_diff('200802','200703');
  +---------------------------------+
  | period_diff('200802', '200703') |
  +---------------------------------+
  |                              11 |

QUARTER

QUARTER(date)

命令说明：返回日期在一年中的季度，取值范围为[1,4]。

参数类型：

  quarter(datetime)
  quarter(varchar)
  quarter(timestamp)
  quarter(date)

返回值类型：BIGINT。

示例：

  select quarter(datetime '2008-04-01 12:12:12');
  +-----------------------------------------+
  | quarter(DATETIME '2008-04-01 12:12:12') |
  +-----------------------------------------+
  |                                       2 |

  select quarter('2008-04-01');
  +-----------------------+
  | quarter('2008-04-01') |
  +-----------------------+
  |                     2 |

  select quarter(timestamp '2008-04-01 12:12:12');
  +------------------------------------------+
  | quarter(TIMESTAMP '2008-04-01 12:12:12') |
  +------------------------------------------+
  |                                        2 |

  select quarter(date '2008-04-01');
  +----------------------------+
  | quarter(DATE '2008-04-01') |
  +----------------------------+
  |                          2 |

SEC_TO_TIME

SEC_TO_TIME(seconds)

命令说明：将seconds转换为时间。

参数类型：

  sec_to_time(bigint)
  sec_to_time(varchar)

返回值类型：TIME。

示例：

  select sec_to_time(2378);
  +-------------------+
  | sec_to_time(2378) |
  +-------------------+
  | 00:39:38          |

  select sec_to_time('2378');
  +---------------------+
  | sec_to_time('2378') |
  +---------------------+
  | 00:39:38            |

SECOND

SECOND(time)

命令说明：返回时间中的秒，范围为[0,59]。

参数类型：

  second(timestamp)
  second(datetime)
  second(date)
  second(time)
  second(varchar)

返回值类型：BIGINT。

示例：

  select second(timestamp '2019-03-12 12:13:14');
  +-----------------------------------------+
  | second(TIMESTAMP '2019-03-12 12:13:14') |
  +-----------------------------------------+
  |                                      14 |

  select second(datetime '2019-03-12 12:13:14');
  +----------------------------------------+
  | second(DATETIME '2019-03-12 12:13:14') |
  +----------------------------------------+
  |                                     14 |

  select second(date '2019-03-12');
  +---------------------------+
  | second(DATE '2019-03-12') |
  +---------------------------+
  |                         0 |

  select second(time '12:13:14'); 
  +-------------------------+
  | second(TIME '12:13:14') |
  +-------------------------+
  |                      14 |

  select second('12:12:23');
  +--------------------+
  | second('12:12:23') |
  +--------------------+
  |                 23 |

STR_TO_DATE

STR_TO_DATE(str,format)

命令说明：按照指定日期或时间显示格式，将字符串转换为日期或日期时间类型。

format遵从DATE_FORMAT函数中的format格式。
参数类型：
```
  str_to_date(varchar, varchar)
```
返回值类型：DATETIME。

示例：

  select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2017-01-06 10:20:30 |

SUBTIME

SUBTIME(expr1,expr2)

命令说明：返回expr1减去expr2后的时间。

参数类型：

  subtime(date, varchar)
  subtime(datetime, varchar)
  subtime(timestamp, varchar)
  subtime(time, varchar)
  subtime(varchar, varchar)

返回值类型：DATETIME。

示例：

  select subtime(date '2018-10-31','0:1:1');
  +-------------------------------------+
  | subtime(DATE '2018-10-31', '0:1:1') |
  +-------------------------------------+
  |                 2018-10-30 23:58:59 |

  select subtime(datetime '2018-10-31 12:12:12','0:1:1');
  +--------------------------------------------------+
  | subtime(DATETIME '2018-10-31 12:12:12', '0:1:1') |
  +--------------------------------------------------+
  |                              2018-10-31 12:11:11 |

  select subtime(timestamp '2018-10-31 12:12:12','0:1:1');
  +---------------------------------------------------+
  | subtime(TIMESTAMP '2018-10-31 12:12:12', '0:1:1') |
  +---------------------------------------------------+
  |                               2018-10-31 12:11:11 |

  select subtime(time '12:12:12','0:1:1');
  +-----------------------------------+
  | subtime(TIME '12:12:12', '0:1:1') |
  +-----------------------------------+
  | 12:11:11                          |
  +-----------------------------------+

  select subtime('2018-10-31 23:59:59','0:1:1');
  +-----------------------------------------+
  | subtime('2018-10-31 23:59:59', '0:1:1') |
  +-----------------------------------------+
  | 2018-10-31 23:58:58                     |

SYSDATE

SYSDATE()

命令说明：获取系统时间。
返回值类型：DATETIME。

示例：

  select sysdate();
  +---------------------+
  | sysdate()           |
  +---------------------+
  | 2019-05-26 00:47:21 |

TIME

TIME(expr)

命令说明：以字符串形式返回expr中的时间。

参数类型：

  time(varchar)
  time(datetime)
  time(timestamp)

返回值类型：VARCHAR。

示例：

  select time('2003-12-31 01:02:03');
  +-----------------------------+
  | time('2003-12-31 01:02:03') |
  +-----------------------------+
  | 01:02:03                    |

  select time(datetime '2003-12-31 01:02:03');
  +--------------------------------------+
  | time(DATETIME '2003-12-31 01:02:03') |
  +--------------------------------------+
  | 01:02:03                             |

  select time(timestamp '2003-12-31 01:02:03');
  +---------------------------------------+
  | time(TIMESTAMP '2003-12-31 01:02:03') |
  +---------------------------------------+
  | 01:02:03                              |

TIME_FORMAT

TIME_FORMAT(time,format)

命令说明：按照format指定的格式，以字符串格式显示时间time。

format遵从DATE_FORMAT函数中的format格式。

参数类型：

  time_format(varchar, varchar) 
  time_format(timestamp, varchar)
  time_format(datetime, varchar)
  time_format(time, varchar) 
  time_format(date, varchar)

返回值类型：VARCHAR。

示例：

  select time_format('12:00:00', '%H %k %h %I %l');
  +-------------------------------------------+
  | time_format('12:00:00', '%H %k %h %I %l') |
  +-------------------------------------------+
  | 12 12 12 12 12                            |

  select time_format(timestamp '1998-01-01 23:00:00','%H %k %h %I %l')as result;
  +----------------+
  | result         |
  +----------------+
  | 23 23 11 11 11 |

  select time_format(datetime '1998-01-01 23:00:00','%H %k %h %I %l')as result;
  +----------------+
  | result         |
  +----------------+
  | 23 23 11 11 11 |

  select time_format(time '23:00:00','%H %k %h %I %l');
  +------------------------------------------------+
  | time_format(TIME '23:00:00', '%H %k %h %I %l') |
  +------------------------------------------------+
  | 23 23 11 11 11                                 |

  select time_format(date '1998-01-01','%H %k %h %I %l');
  +--------------------------------------------------+
  | time_format(DATE '1998-01-01', '%H %k %h %I %l') |
  +--------------------------------------------------+
  | 00 0 12 12 12                                    |

TIME_TO_SEC

TIME_TO_SEC(time)

命令说明：返回time转换为秒的结果。

参数类型：

  time_to_sec(varchar)
  time_to_sec(datetime)
  time_to_sec(timestamp)
  time_to_sec(date)
  time_to_sec(time)

返回值类型：BIGINT。

示例：

  select time_to_sec(datetime '2009-12-12 22:23:00');
  +---------------------------------------------+
  | time_to_sec(DATETIME '2009-12-12 22:23:00') |
  +---------------------------------------------+
  |                                       80580 |

  select time_to_sec(timestamp '2009-12-12 22:23:00');
  +----------------------------------------------+
  | time_to_sec(TIMESTAMP '2009-12-12 22:23:00') |
  +----------------------------------------------+
  |                                        80580 |

  select time_to_sec(date '2009-12-12');
  +--------------------------------+
  | time_to_sec(DATE '2009-12-12') |
  +--------------------------------+
  |                              0 |

  select time_to_sec(time '12:12:12');
  +------------------------------+
  | time_to_sec(TIME '12:12:12') |
  +------------------------------+
  |                        43932 |

  select time_to_sec('22:23:00');
  +-------------------------+
  | time_to_sec('22:23:00') |
  +-------------------------+
  |                   80580 |

TIMEDIFF

TIMEDIFF(expr1,expr2)

命令说明：返回expr1减去expr2后的时间，与SUBTIME作用相同。

参数类型：

  timediff(time, varchar)
  timediff(time, time)
  timediff(varchar, varchar)

返回值类型：DATETIME。

示例：

  select timediff(time '12:00:00','10:00:00');
  +---------------------------------------+
  | timediff(TIME '12:00:00', '10:00:00') |
  +---------------------------------------+
  | 02:00:00                              |

  select timediff('12:00:00','10:00:00');
  +----------------------------------+
  | timediff('12:00:00', '10:00:00') |
  +----------------------------------+
  | 02:00:00                         |

  select timediff(time '12:00:00',time '10:00:00');
  +--------------------------------------------+
  | timediff(TIME '12:00:00', TIME '10:00:00') |
  +--------------------------------------------+
  | 02:00:00                                   |

TIMESTAMP

TIMESTAMP(expr)

命令说明：返回expr表示的日期或日期时间。
参数类型：
```
  timestamp(date)
  timestamp(varchar)
```
返回值类型：DATETIME。

示例：

  select timestamp(date '2019-05-27');
  +------------------------------+
  | timestamp(DATE '2019-05-27') |
  +------------------------------+
  | 2019-05-27 00:00:00          |

  select timestamp('2019-05-27');
  +-------------------------+
  | timestamp('2019-05-27') |
  +-------------------------+
  |     2019-05-27 00:00:00 |

TIMESTAMPADD

TIMESTAMPADD(unit,interval,datetime_expr)

命令说明：将interval添加到日期或日期时间表达式datetime_expr中。 interval的单位由unit规定。

unit可取值为: second、minute、hour、day、week、month、quarter、year。

参数类型：

  timestampadd(varchar, varchar, timestamp) 
  timestampadd(varchar, bigint, timestamp)
  timestampadd(varchar, varchar, date)
  timestampadd(varchar, bigint, date)
  timestampadd(varchar, varchar, datetime)
  timestampadd(varchar, bigint, datetime) 
  timestampadd(varchar, varchar, varchar)
  timestampadd(varchar, bigint, varchar)

返回值类型：DATETIME。

示例：

  select timestampadd(second,'1',timestamp '2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 12:12:13 |

  select timestampadd(second,1,timestamp '2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 12:12:13 |

  select timestampadd(second,'1',date '2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 00:00:01 |

  select timestampadd(second,1,date '2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 00:00:01 |

  select timestampadd(second,'1',datetime '2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 12:12:13 |

  select timestampadd(second,1,datetime '2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 12:12:13 |

  select timestampadd(second,'1','2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 12:12:13 |

  select timestampadd(second,1,'2003-01-02 12:12:12')as result;
  +---------------------+
  | result              |
  +---------------------+
  | 2003-01-02 12:12:13 |

  select timestampadd(second,1,'2003-01-02 12:12:12');
  +--------------------------------------------------+
  | timestampadd('second', 1, '2003-01-02 12:12:12') |
  +--------------------------------------------------+
  | 2003-01-02 12:12:13                              |

  select timestampadd(minute,8820,'2019-08-24 09:00:00');
  +-----------------------------------------------------+
  | timestampadd('MINUTE', 8820, '2019-08-24 09:00:00') |
  +-----------------------------------------------------+
  | 2019-08-30 12:00:00                                 |

  select timestampadd(hour,1,'2003-01-02 12:12:12');
  +------------------------------------------------+
  | timestampadd('hour', 1, '2003-01-02 12:12:12') |
  +------------------------------------------------+
  | 2003-01-02 13:12:12                            |

  select timestampadd(day,1,'2003-01-02 12:12:12');
  +-----------------------------------------------+
  | timestampadd('day', 1, '2003-01-02 12:12:12') |
  +-----------------------------------------------+
  | 2003-01-03 12:12:12                           |

  select timestampadd(week,1,'2003-01-02 12:12:12');
  +------------------------------------------------+
  | timestampadd('week', 1, '2003-01-02 12:12:12') |
  +------------------------------------------------+
  | 2003-01-09 12:12:12                            |

  select timestampadd(month,1,'2003-01-02 12:12:12');
  +-------------------------------------------------+
  | timestampadd('month', 1, '2003-01-02 12:12:12') |
  +-------------------------------------------------+
  | 2003-02-02 12:12:12                             |

  select timestampadd(year,1,'2003-01-02 12:12:12');
  +------------------------------------------------+
  | timestampadd('year', 1, '2003-01-02 12:12:12') |
  +------------------------------------------------+
  | 2004-01-02 12:12:12                            |

  select timestampadd(quarter,1,'2003-01-02 12:12:12');
  +---------------------------------------------------+
  | timestampadd('quarter', 1, '2003-01-02 12:12:12') |
  +---------------------------------------------------+
  | 2003-04-02 12:12:12                               |

TIMESTAMPDIFF

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

命令说明：返回日期或日期时间表达式datetime_expr1减去datetime_expr2后的结果，结果的单位由unit指定。

unit可取值为：second、minute、hour、day、week、month、quarter或year。

使用方法和TIMESTAMPADD相同。

参数类型：

  timestampdiff(varchar, timestamp, timestamp)
  timestampdiff(varchar, date, date)
  timestampdiff(varchar, datetime, datetime)
  timestampdiff(varchar, varchar, varchar)

返回值类型：BIGINT。

示例：

  select timestampdiff(second,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
  +---------+
  | result  |
  +---------+
  | 7689600 |

  select timestampdiff(minute,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
  +--------+
  | result |
  +--------+
  | 128160 |

  select timestampdiff(hour,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
  +--------+
  | result |
  +--------+
  |   2136 |

  select timestampdiff(day,timestamp '2003-02-01',timestamp '2003-05-01')as result;
  +--------+
  | result |
  +--------+
  |     89 |

  select timestampdiff(day,date '2003-02-01',date '2003-05-01');
  +------------------------------------------------------------+
  | timestampdiff('day', DATE '2003-02-01', DATE '2003-05-01') |
  +------------------------------------------------------------+
  |                                                         89 |

  select timestampdiff(day,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
  +--------+
  | result |
  +--------+
  |     89 |

  select timestampdiff(day,'2003-02-01','2003-05-01');
  +--------------------------------------------------+
  | timestampdiff('day', '2003-02-01', '2003-05-01') |
  +--------------------------------------------------+
  |                                               89 |

  select timestampdiff(week,'2003-02-01','2003-05-01');
  +---------------------------------------------------+
  | timestampdiff('week', '2003-02-01', '2003-05-01') |
  +---------------------------------------------------+
  |                                                12 |

  select timestampdiff(quarter,'2003-02-01','2003-05-01');
  +------------------------------------------------------+
  | timestampdiff('quarter', '2003-02-01', '2003-05-01') |
  +------------------------------------------------------+
  |                                                    1 |

  select timestampdiff(month,'2003-02-01','2003-05-01');
  +----------------------------------------------------+
  | timestampdiff('month', '2003-02-01', '2003-05-01') |
  +----------------------------------------------------+
  |                                                  3 |

  select timestampdiff(year,datetime '2003-02-01 10:12:13',datetime '2001-05-01 10:12:13')as result;
  +--------+
  | result |
  +--------+
  |     -1 |

TO_DAYS

TO_DAYS(date)

命令说明：根据给定日期date，返回自0年开始的天数。

参数类型：

  to_days(date)
  to_days(time)
  to_days(varchar)
  to_days(timestamp)
  to_days(datetime)

返回值类型：BIGINT。

示例：

  select to_days(date '2018-12-12');
  +----------------------------+
  | to_days(DATE '2018-12-12') |
  +----------------------------+
  |                     737405 |

  select to_days(time '12:12:12');
  +--------------------------+
  | to_days(TIME '12:12:12') |
  +--------------------------+
  |                   737572 |

  select to_days(now());
  +----------------+
  | to_days(now()) |
  +----------------+
  |         737572 |

上述查询等价于to_days(curdate())

  select to_days(curdate());
  +--------------------+
  | to_days(curdate()) |
  +--------------------+
  |             737573 |

  select to_days(datetime '2019-09-08 12:12:12');
  +-----------------------------------------+
  | to_days(DATETIME '2019-09-08 12:12:12') |
  +-----------------------------------------+
  |                                  737675 |

  select to_days('2019-09-08 12:12:12');
  +--------------------------------+
  | to_days('2019-09-08 12:12:12') |
  +--------------------------------+
  |                         737675 |

  select to_days(timestamp '2019-09-08 12:12:12');
  +------------------------------------------+
  | to_days(TIMESTAMP '2019-09-08 12:12:12') |
  +------------------------------------------+
  |                                   737675 |

TO_SECONDS

TO_SECONDS(expr)

命令说明：根据给定的expr，返回自0年开始的秒数。

参数类型：

  to_seconds(date)
  to_seconds(datetime)
  to_seconds(timestamp)
  to_seconds(varchar)
  to_seconds(time)

返回值类型：BIGINT。

示例：

  select to_seconds(date '2019-09-08');
  +-------------------------------+
  | to_seconds(DATE '2019-09-08') |
  +-------------------------------+
  |                   63735120000 |

  select to_seconds(datetime '2019-09-08 09:09:00');
  +--------------------------------------------+
  | to_seconds(DATETIME '2019-09-08 09:09:00') |
  +--------------------------------------------+
  |                                63735152940 |

  select to_seconds(timestamp '2019-09-08 09:09:00');
  +---------------------------------------------+
  | to_seconds(TIMESTAMP '2019-09-08 09:09:00') |
  +---------------------------------------------+
  |                                 63735152940 |

执行以下SQL，系统将返回'09:09:00'加上curdate()后的结果。

  select to_seconds(time '09:09:00');
  +-----------------------------+
  | to_seconds(TIME '09:09:00') |
  +-----------------------------+
  |                 63726253740 |

  select to_seconds('2019-09-08');
  +--------------------------+
  | to_seconds('2019-09-08') |
  +--------------------------+
  |              63735120000 |

UNIX_TIMESTAMP

UNIX_TIMESTAMP([date])

命令说明：UNIX_TIMESTAMP()返回自'1970-01-01 00:00:00' UTC以来秒数的Unix时间戳。UNIX_TIMESTAMP(date) 将参数的值返回为'1970-01-01 00:00:00'UTC后的秒数的Unix时间戳。

参数类型：

  unix_timestamp()
  unix_timestamp(varchar)
  unix_timestamp(timestamp)
  unix_timestamp(date)
  unix_timestamp(datetime)

返回值类型：BIGINT。

示例：

  select unix_timestamp();
  +------------------+
  | unix_timestamp() |
  +------------------+
  |       1558935850 |

  select unix_timestamp(timestamp '2019-09-08 12:12:12');
  +-------------------------------------------------+
  | unix_timestamp(TIMESTAMP '2019-09-08 12:12:12') |
  +-------------------------------------------------+
  |                                      1567915932 |

  select unix_timestamp(date '2019-09-08');
  +-----------------------------------+
  | unix_timestamp(DATE '2019-09-08') |
  +-----------------------------------+
  |                        1567872000 |

  select unix_timestamp(datetime '2019-09-08 12:12:12');
  +------------------------------------------------+
  | unix_timestamp(DATETIME '2019-09-08 12:12:12') |
  +------------------------------------------------+
  |                                     1567915932 |

  select unix_timestamp('2019-09-08 12:12:12');
  +---------------------------------------+
  | unix_timestamp('2019-09-08 12:12:12') |
  +---------------------------------------+
  |                            1567915932 |

UTC_DATE

UTC_DATE()

命令说明：返回UTC日期。
返回值类型：VARCHAR。

示例：

  select utc_date();
  +------------+
  | utc_date() |
  +------------+
  | 2019-05-27 |

UTC_TIME

UTC_TIME()

命令说明：返回UTC时间。
返回值类型：VARCHAR。

示例：

  select utc_time();
  +------------+
  | utc_time() |
  +------------+
  | 05:53:19   |

UTC_TIMESTAMP

utc_timestamp()

命令说明：返回UTC时间戳。
返回值类型：VARCHAR。

示例：

  select utc_timestamp();
  +---------------------+
  | utc_timestamp()     |
  +---------------------+
  | 2019-05-27 05:55:15 |

WEEK

WEEK(date[,mode])

命令说明：返回date对应的周数，即date是日期年份中的哪一周。
- date是要获取周数的日期。
- mode可选参数，用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始，返回的周数应在0到52之间或0到53之间。mode支持的格式如下表所示。
  
  模式一周的第一天范围
  0 星期日 0-53
  1 星期一 0-53
  2 星期日 1-53
  3 星期一 1-53
  4 星期日 0-53
  5 星期一 0-53
  6 星期日 1-53
  7 星期一 1-53

模式	一周的第一天	范围
0	星期日	0-53
1	星期一	0-53
2	星期日	1-53
3	星期一	1-53
4	星期日	0-53
5	星期一	0-53
6	星期日	1-53
7	星期一	1-53

参数类型：

  week(varchar)
  week(varchar, bigint)
  week(date)
  week(date, bigint)
  week(datetime)
  week(datetime, bigint)
  week(timestamp)
  week(timestamp, bigint)

返回值类型：BIGINT。

示例：

  select week('2019-05-27');
  +--------------------+
  | week('2019-05-27') |
  +--------------------+
  |                 21 |

  select week('2008-02-20',1);
  +-----------------------+
  | week('2008-02-20', 1) |
  +-----------------------+
  |                     8 |

  select week(date '2008-02-20');
  +-------------------------+
  | week(DATE '2008-02-20') |
  +-------------------------+
  |                       7 |

  select week(date '2008-02-20',1);
  +----------------------------+
  | week(DATE '2008-02-20', 1) |
  +----------------------------+
  |                          8 |

  select week(datetime '2008-02-20 00:00:00',1);
  +-----------------------------------------+
  | week(DATETIME '2008-02-20 00:00:00', 1) |
  +-----------------------------------------+
  |                                       8 |

  select week(datetime '2008-02-20 00:00:00');
  +--------------------------------------+
  | week(DATETIME '2008-02-20 00:00:00') |
  +--------------------------------------+
  |                                    7 |

  select week(timestamp '2008-02-20 00:00:00');
  +---------------------------------------+
  | week(TIMESTAMP '2008-02-20 00:00:00') |
  +---------------------------------------+
  |                                     7 |

  select week(timestamp '2008-02-20 00:00:00',1);
  +------------------------------------------+
  | week(TIMESTAMP '2008-02-20 00:00:00', 1) |
  +------------------------------------------+
  |                                        8 |

WEEKDAY

WEEKDAY(date)

命令说明：返回date对应的工作日即0= Monday,1= Tuesday,... 6= Sunday。

参数类型：

  weekday(timestamp)
  weekday(datetime)
  weekday(date)
  weekday(varchar)

返回值类型：BIGINT。

示例：

  select weekday(timestamp '2019-05-27 00:09:00');
  +------------------------------------------+
  | weekday(TIMESTAMP '2019-05-27 00:09:00') |
  +------------------------------------------+
  |                                        0 |

  select weekday(datetime '2019-05-27 00:09:00');
  +-----------------------------------------+
  | weekday(DATETIME '2019-05-27 00:09:00') |
  +-----------------------------------------+
  |                                       0 |

  select weekday(date '2019-05-27 00:09:00');
  +-------------------------------------+
  | weekday(DATE '2019-05-27 00:09:00') |
  +-------------------------------------+
  |                                   0 |

  select weekday('2019-05-27');
  +-----------------------+
  | weekday('2019-05-27') |
  +-----------------------+
  |                     0 |

WEEKOFYEAR

WEEKOFYEAR(date)

命令说明：返回date对应的日历周，取值范围为[1,53]。

参数类型：

  weekofyear(timestamp)
  weekofyear(datetime)
  weekofyear(date)
  weekofyear(varchar)

返回值类型：BIGINT。

示例：

  select weekofyear(timestamp '2019-05-27 09:00:00');
  +---------------------------------------------+
  | weekofyear(TIMESTAMP '2019-05-27 09:00:00') |
  +---------------------------------------------+
  |                                          22 |

  select weekofyear(datetime '2019-05-27 09:00:00');
  +--------------------------------------------+
  | weekofyear(DATETIME '2019-05-27 09:00:00') |
  +--------------------------------------------+
  |                                         22 |

  select weekofyear(date '2019-05-27');
  +-------------------------------+
  | weekofyear(DATE '2019-05-27') |
  +-------------------------------+
  |                            22 |

  select weekofyear('2019-05-27');
  +--------------------------+
  | weekofyear('2019-05-27') |
  +--------------------------+
  |                       22 |

YEAR

YEAR(date)

命令说明：返回date中的年份。

参数类型：

  year(timestamp)
  year(datetime)
  year(date)
  year(time)
  year(varchar)

返回值类型：BIGINT。

示例：

  select year(timestamp '2019-05-27 00:00:00');
  +---------------------------------------+
  | year(TIMESTAMP '2019-05-27 00:00:00') |
  +---------------------------------------+
  |                                  2019 |

  select year(datetime '2019-05-27 00:00:00');
  +--------------------------------------+
  | year(DATETIME '2019-05-27 00:00:00') |
  +--------------------------------------+
  |                                 2019 |

  select year(date '2019-05-27');
  +-------------------------+
  | year(DATE '2019-05-27') |
  +-------------------------+
  |                    2019 |

执行以下SQL，系统将返回'00:00:00'加上curdate时间部分后的结果，结果数据类型为字符串。

  select year(time '00:00:00');
  +-----------------------+
  | year(TIME '00:00:00') |
  +-----------------------+
  |                  2019 |

  select year('2019-05-27');
  +--------------------+
  | year('2019-05-27') |
  +--------------------+
  |               2019 |

YEARWEEK

YEARWEEK(date)
YEARWEEK(date,mode)

命令说明：返回日期的年份和星期。

返回结果中的年份可能与一年中第一周和最后一周的日期参数中的年份不同。

mode与WEEK函数中的mode作用相同。对于单参数语法，mode值为0。

参数类型：

  yearweek(timestamp)
  yearweek(timestamp, bigint)
  yearweek(datetime)
  yearweek(datetime, bigint)
  yearweek(date, bigint) 
  yearweek(date) 
  yearweek(varchar)
  yearweek(varchar, bigint)

返回值类型：BIGINT。

示例：

  select yearweek(timestamp '2019-05-27 00:00:00');
  +-------------------------------------------+
  | yearweek(TIMESTAMP '2019-05-27 00:00:00') |
  +-------------------------------------------+
  |                                    201921 |

  select yearweek(timestamp '2019-05-27 00:00:00',1);
  +----------------------------------------------+
  | yearweek(TIMESTAMP '2019-05-27 00:00:00', 1) |
  +----------------------------------------------+
  |                                       201922 |

  select yearweek(datetime '2019-05-27 00:00:00');
  +------------------------------------------+
  | yearweek(DATETIME '2019-05-27 00:00:00') |
  +------------------------------------------+
  |                                   201921 |

  select yearweek(datetime '2019-05-27 00:00:00',1);
  +---------------------------------------------+
  | yearweek(DATETIME '2019-05-27 00:00:00', 1) |
  +---------------------------------------------+
  |                                      201922 |

  select yearweek(date '2019-05-27',1);
  +--------------------------------+
  | yearweek(DATE '2019-05-27', 1) |
  +--------------------------------+
  |                         201922 |

  select yearweek(date '2019-05-27');
  +-----------------------------+
  | yearweek(DATE '2019-05-27') |
  +-----------------------------+
  |                      201921 |

  select yearweek('2019-05-27');
  +------------------------+
  | yearweek('2019-05-27') |
  +------------------------+
  |                 201921 |

  select yearweek('2019-05-27',1);
  +---------------------------+
  | yearweek('2019-05-27', 1) |
  +---------------------------+
  |                    201922 |

日期和时间函数_系统函数_分析型数据库MySQL版

日期和时间函数

ADDDATE/DATE_ADD

ADDTIME

CONVERT_TZ

CURDATE

CURTIME

DATE

DATE_FORMAT

SUBDATE/DATE_SUB

DATEDIFF

DAY/DAYOFMONTH

DAYNAME

DAYOFWEEK

DAYOFYEAR

EXTRACT

FROM_DAYS

FROM_UNIXTIME

HOUR

LAST_DAY

LOCALTIME/LOCALTIMESTAMP/NOW

MAKEDATE

MAKETIME

MINUTE

MONTH

MONTHNAME

PERIOD_ADD

PERIOD_DIFF

QUARTER

SEC_TO_TIME

SECOND

STR_TO_DATE

SUBTIME

SYSDATE

TIME

TIME_FORMAT

TIME_TO_SEC

TIMEDIFF

TIMESTAMP

TIMESTAMPADD

TIMESTAMPDIFF

TO_DAYS

TO_SECONDS

UNIX_TIMESTAMP

UTC_DATE

UTC_TIME

UTC_TIMESTAMP

WEEK

WEEKDAY

WEEKOFYEAR

YEAR

YEARWEEK

版权声明

评论