日期和时间函数
- 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
参数类型:
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 |
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论