MySQL 日期与时间方面的函数
| 函数名称 | 函数功能说明 |
ADDDATE() |
添加日期 |
ADDTIME() |
添加时间 |
CONVERT_TZ() |
转换不同时区 |
CURDATE() |
返回当前日期 |
CURRENT_DATE() 与 CURRENT_DATE |
等同于 CURDATE() |
CURRENT_TIME() 与 CURRENT_TIME |
等同于 CURTIME() |
CURRENT_TIMESTAMP() 与 CURRENT_TIMESTAMP |
等同于 NOW() |
CURTIME() |
返回当前时间 |
DATE_ADD() |
添加两个日期 |
DATE_FORMAT() |
按指定方式格式化日期 |
DATE_SUB() |
求解两个日期的间隔 |
DATE() |
提取日期或日期时间表达式中的日期部分 |
DATEDIFF() |
求解两个日期的间隔 |
DAY() |
等同于 DAYOFMONTH() |
DAYNAME() |
返回星期中某天的名称 |
DAYOFMONTH() |
返回一月中某天的序号（1-31） |
DAYOFWEEK() |
返回参数所定影的一周中某天的索引值 |
DAYOFYEAR() |
返回一年中某天的序号（1-366） |
EXTRACT |
提取日期中的相应部分 |
FROM_DAYS() |
将一个天数序号转变为日期值 |
FROM_UNIXTIME() |
将日期格式化为 UNIX 的时间戳 |
HOUR() |
提取时间 |
LAST_DAY |
根据参数，返回月中最后一天 |
LOCALTIME() 和 LOCALTIME |
等同于 NOW() |
LOCALTIMESTAMP 和 LOCALTIMESTAMP() |
等同于 NOW() |
MAKEDATE() |
基于给定参数年份和所在年中的天数序号，返回一个日期 |
MAKETIME |
MAKETIME() |
MICROSECOND() |
返回参数所对应的毫秒数 |
MINUTE() |
返回参数对应的分钟数 |
MONTH() |
返回传入日期所对应的月序数 |
MONTHNAME() |
返回月的名称 |
NOW() |
返回当前日期与时间 |
PERIOD_ADD() |
为年-月组合日期添加一个时段 |
PERIOD_DIFF() |
返回两个时段之间的月份差值 |
QUARTER() |
返回日期参数所对应的季度序号 |
SEC_TO_TIME() |
将描述转变成 'HH:MM:SS' 的格式 |
SECOND() |
返回秒序号（0-59） |
STR_TO_DATE() |
将字符串转变为日期 |
SUBDATE() |
三个参数的版本相当于 DATE_SUB() |
SUBTIME() |
计算时间差值 |
SYSDATE() |
返回函数执行时的时间 |
TIME_FORMAT() |
提取参数中的时间部分 |
TIME_TO_SEC() |
将参数转化为秒数 |
TIME() |
提取传入表达式的时间部分 |
TIMEDIFF() |
计算时间差值 |
TIMESTAMP() |
单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 |
TIMESTAMPADD() |
为日期时间表达式添加一个间隔 INTERVAL |
TIMESTAMPDIFF() |
从日期时间表达式中减去一个间隔 INTERVAL |
TO_DAYS() |
返回转换成天数的日期参数 |
UNIX_TIMESTAMP() |
返回一个 UNIX 时间戳 |
UTC_DATE() |
返回当前的 UTC 日期 |
UTC_TIME() |
返回当前的 UTC 时间 |
UTC_TIMESTAMP() |
返回当前的 UTC 时间与日期 |
WEEK() |
返回周序号 |
WEEKDAY() |
返回某天在星期中的索引值 |
WEEKOFYEAR() |
返回日期所对应的星期在一年当中的序号（1-53） |
YEAR() |
返回年份 |
YEARWEEK() |
返回年份及星期序号 |
获得当前日期
select now(), current_timestamp(), current_timestamp, CURDATE(), CURTIME();
时间转换
MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09 select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09 select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09 select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30 select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
MySQL （日期、天数）转换函数：to_days(date), from_days(days)
select to_days('0000-00-00'); -- 0 select to_days('2008-08-08'); -- 733627
MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)
select time_to_sec('01:00:05'); -- 3605 select sec_to_time(3605); -- '01:00:05'
MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)
select makedate(2001,31); -- '2001-01-31' select makedate(2001,32); -- '2001-02-01' select maketime(12,15,30); -- '12:15:30'
MySQL （Unix 时间戳、日期）转换函数
unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)
下面是示例：
select unix_timestamp(); -- 1218290027 select unix_timestamp('2008-08-08'); -- 1218124800 select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800 select from_unixtime(1218290027); -- '2008-08-09 21:53:47' select from_unixtime(1218124800); -- '2008-08-08 00:00:00' select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
FROM_UNIXTIME(unix_timestamp,format),DATE_FORMAT(date,format)中format
下表中列出了一些可用于 format 字符串的标识符。格式标识符第一个字符必须是%字符。
| 格式标识符 | 说明 |
%a |
一星期中每天名称的缩写（Sun...Sat） |
%b |
月份的缩写（Jan...Dec） |
%c |
月份的数字表现形式（0...12） |
%D |
带有英语后缀的一个月中的每一天的名称（0th、1st、2nd、3rd） |
%d |
用数字形式表现的每月中的每一天（00...31） |
%e |
用数字形式表现的每月中的每一天（0...31） |
%f |
毫秒（000000...999999） |
%H |
24时制显示的小时（00...23） |
%h |
12时制显示的小时（01...12） |
%I |
12时制显示的小时（01...12） |
%i |
以数字形式表现的分钟数（00...59） |
%j |
一年中的每一天（001...366） |
%k |
24时制小时的另一种表现格式（0...23） |
%l |
12时制小时的另一种表现格式（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） |
%U |
星期（00...53），其中星期天是每星期的开始日 |
%u |
星期（00...53），其中星期一是每星期的开始日 |
%V |
星期（01...53），其中星期天是每星期的开始日，和 %X 一起使用 |
%v |
星期（01...53），其中星期一是每星期的开始日，和 %x 一起使用 |
%W |
一星期中各日名称（Sunday...Saturday） |
%w |
一星期中各日名称（0代表星期日，6代表星期六，以此类推） |
%X |
某星期所处年份。其中，星期天是每星期的开始日，采用4位数字形式表现，和 %V一起使用 |
%x |
某星期所处年份。其中，星期一是每星期的开始日，采用4位数字形式表现，和 %V 一起使用 |
%Y |
4位数字表示的年份 |
%y |
2位数字表示的年份 |
%% |
符号%的字面值 |
| %x（x为斜体） | 字符x的字面值，x指以上未列出的任何字符 |
MySQL 日期时间计算函数
MySQL 为日期增加一个时间间隔：date_add()
set @dt = now(); select date_add(@dt, interval 1 day); -- add 1 day select date_add(@dt, interval 1 hour); -- add 1 hour select date_add(@dt, interval 1 minute); -- ... select date_add(@dt, interval 1 second); select date_add(@dt, interval 1 microsecond); select date_add(@dt, interval 1 week); select date_add(@dt, interval 1 month); select date_add(@dt, interval 1 quarter); select date_add(@dt, interval 1 year);
MySQL 为日期减去一个时间间隔：date_sub()
DATE_ADD(date,INTERVAL expr unit) 与 DATE_SUB(date,INTERVAL expr unit)
执行日期计算的两种函数。date 是一个用来指定开始日期的 DATETIME 或 DATE 值。expr 是一种以字符串形式呈现的表达式，用来指定从开始日期增加或减少的间隔值。如果是负的间隔值，则 expr 值的第一个字符是-号。unit 是一个单位关键字，用来指定expr表达式应该采取的单位。
INTERVAL 关键字与单位说明符都不区分大小写。
下表列出了每个单位数值所对应的 expr 参数的期望格式。
| 单位所能取的值 | 期望的expr格式 |
MICROSECOND |
毫秒 |
SECOND |
秒 |
MINUTE |
分 |
HOUR |
小时 |
DAY |
日 |
WEEK |
周 |
MONTH |
月 |
QUARTER |
季度 |
YEAR |
年 |
SECOND_MICROSECOND |
'秒.毫秒' |
MINUTE_MICROSECOND |
'分.毫秒' |
MINUTE_SECOND |
'分:秒' |
HOUR_MICROSECOND |
'小时.毫秒' |
HOUR_SECOND |
'小时:分:秒' |
HOUR_MINUTE |
'小时:分' |
DAY_MICROSECOND |
'日.毫秒' |
DAY_SECOND |
'日 小时:分:秒' |
DAY_MINUTE |
'日 小时:分' |
DAY_HOUR |
'日 小时' |
YEAR_MONTH |
'年-月' |
MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
-- datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。 select datediff('2008-08-08', '2008-08-01'); -- 7 select datediff('2008-08-01', '2008-08-08'); -- -7
MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08 select timediff('08:08:08', '00:00:00'); -- 08:08:08
注意：timediff(time1,time2) 函数的两个参数类型必须相同。
MySQL 时间戳（timestamp）转换、增、减函数：
timestamp(date) -- date to timestamp timestamp(dt,time) -- dt + time timestampadd(unit,interval,datetime_expr) -- timestampdiff(unit,datetime_expr1,datetime_expr2) --
请看示例部分：
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00 select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01 select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01 select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00 select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00 -- timestampadd() 函数类似于 date_add()。 select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1 select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485 select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12 select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7
MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。
MySQL 时区（timezone）转换函数
-- convert_tz(dt,from_tz,to_tz) select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00
时区转换也可以通过 date_add, date_sub, timestampadd 来实现。
select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00 select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00 select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00
参考链接:https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html
