MySQL 5.6常用日期函数

1、ADDDATE 添加时间间隔

用法：

ADDDATE(date,INTERVAL expr unit)

ADDDATE(expr,days)

 

-- 增加时间间隔
select ADDDATE(NOW(),INTERVAL 1 YEAR) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL 1 YEAR) bb;
select ADDDATE(NOW(),INTERVAL 1 MONTH) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL 1 MONTH) bb;
select ADDDATE(NOW(),INTERVAL 1 WEEK) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL 1 WEEK) bb;
select ADDDATE(NOW(),INTERVAL 1 DAY) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL 1 DAY) bb;
select ADDDATE(NOW(),INTERVAL 100 HOUR) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL 100 HOUR) bb;
select ADDDATE(NOW(),INTERVAL 100 MINUTE) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL 100 MINUTE) bb;
select ADDDATE(now(),INTERVAL '0001-01' YEAR_MONTH) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL '0001-01' YEAR_MONTH) bb;
select ADDDATE(NOW(),INTERVAL '1:10:10' HOUR_SECOND) aa,ADDDATE('2014-1-1 01:01:01',INTERVAL '1:10:10' HOUR_SECOND) bb;
-- 增加天数
SELECT ADDDATE(NOW(), 31) aa,ADDDATE('2008-01-02', 31) bb;

官方文档时间间隔定义：

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

2、ADDTIME 添加时间

ADDTIME(expr1,expr2)

 

-- 增加时间
select ADDTIME(NOW(), '30:0:0') aa,ADDTIME('10:10:10', '30:0:0') bb;
select ADDTIME(NOW(), '1 1:1:1.000002') aa,ADDTIME('10:10:10', '1 1:1:1.000002') bb;
select ADDTIME(NOW(), '100 01:01:01.000002') aa,ADDTIME('10:10:10', '100 01:01:01.000002') bb;

3、CONVERT_TZ 时区时间转换

 

CONVERT_TZ(dt,from_tz,to_tz)

 

-- 0时区 -> 东八区
SELECT CONVERT_TZ('2014-01-01 01:00:00','+00:00','+8:00') aa;
-- 西八区 -> 东八区
SELECT CONVERT_TZ('2014-01-01 01:00:00','-8:00','+8:00') aa;

4、当前时间 CURRENT_DATE

-- 当前日期
select CURRENT_DATE() a1,CURRENT_DATE a2,CURDATE() a3;
-- 当前时间
select CURRENT_TIME() a1,CURRENT_TIME a2,CURTIME() a3;
-- 当前时间戳
select CURRENT_TIMESTAMP() a1,CURRENT_TIMESTAMP a2,NOW() a3;
select NOW() a1,LOCALTIME a2,LOCALTIME() a3,LOCALTIMESTAMP  a4,LOCALTIMESTAMP () a5;

 

 

 

5、增加减去时间间隔DATE_ADD, DATE_SUB(SUBDATE)

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

-- 时间加减
select date_add(NOW(),INTERVAL 1 YEAR) aa,date_add('2014-1-1 01:01:01',INTERVAL 1 YEAR) bb;
select date_add(NOW(),INTERVAL 1 MONTH) aa,date_add('2014-1-1 01:01:01',INTERVAL 1 MONTH) bb;
select date_add(NOW(),INTERVAL 1 WEEK) aa,date_add('2014-1-1 01:01:01',INTERVAL 1 WEEK) bb;
select date_add(NOW(),INTERVAL 1 DAY) aa,date_add('2014-1-1 01:01:01',INTERVAL 1 DAY) bb;
select date_add(NOW(),INTERVAL 100 HOUR) aa,date_add('2014-1-1 01:01:01',INTERVAL 100 HOUR) bb;
select date_add(NOW(),INTERVAL 100 MINUTE) aa,date_add('2014-1-1 01:01:01',INTERVAL 100 MINUTE) bb;
select date_add(now(),INTERVAL '0001-01' YEAR_MONTH) aa,date_add('2014-1-1 01:01:01',INTERVAL '0001-01' YEAR_MONTH) bb;
select date_add(NOW(),INTERVAL '1:10:10' HOUR_SECOND) aa,date_add('2014-1-1 01:01:01',INTERVAL '1:10:10' HOUR_SECOND) bb;

select DATE_SUB(NOW(),INTERVAL 1 YEAR) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL 1 YEAR) bb;
select DATE_SUB(NOW(),INTERVAL 1 MONTH) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL 1 MONTH) bb;
select DATE_SUB(NOW(),INTERVAL 1 WEEK) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL 1 WEEK) bb;
select DATE_SUB(NOW(),INTERVAL 1 DAY) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL 1 DAY) bb;
select DATE_SUB(NOW(),INTERVAL 100 HOUR) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL 100 HOUR) bb;
select DATE_SUB(NOW(),INTERVAL 100 MINUTE) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL 100 MINUTE) bb;
select DATE_SUB(now(),INTERVAL '0001-01' YEAR_MONTH) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL '0001-01' YEAR_MONTH) bb;
select DATE_SUB(NOW(),INTERVAL '1:10:10' HOUR_SECOND) aa,DATE_SUB('2014-1-1 01:01:01',INTERVAL '1:10:10' HOUR_SECOND) bb;

6、日期格式化DATE_FORMAT(date,format)

官方定义参数：

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal “%” character
%x	x, for any “x” not listed above

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') aa;
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s %W') aa;

7、获取日期部分数据

-- 日期部分获取
select DATE(NOW()) aa;
select TIME(NOW()) aa;
select YEAR(NOW()) aa;
select QUARTER(NOW()) aa;
select MONTH(NOW()) aa,MONTHNAME(NOW()) bb;
select DAY(NOW()) aa;
select HOUR(NOW()) aa;
select MINUTE(NOW()) aa;
select SECOND(NOW()) aa;
select MICROSECOND(NOW()) aa;
select WEEKDAY(NOW()) aa;
select WEEKOFYEAR(NOW()) aa;
select YEARWEEK(NOW()) aa;
select DAYNAME(NOW()) aa;
select DAYOFMONTH(NOW()) aa;
select DAYOFWEEK(NOW()) aa;
select DAYOFYEAR(NOW()) aa;

8、DATEDIFF,TIMEDIFF 日期、时间相减

-- 两个时间相减(日期部分相减,返回天数)
select DATEDIFF('2014-1-1 10:00:00','2014-2-2 01:00:00') aa;
select TIMEDIFF('2014-1-1 10:10:10','2014-2-2 01:20:30') aa;
SELECT TIMESTAMPDIFF(YEAR,'2003-02-01','2003-05-01') aa;
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') aa;
SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');
SELECT TIMESTAMPDIFF(HOUR,'2003-02-01','2003-05-01');

9、EXTRACT(unit FROM date)提取日期数据

-- 提取日期数据
select EXTRACT(YEAR FROM '2014-1-1') aa;
select EXTRACT(MONTH FROM NOW()) aa;
select EXTRACT(DAY FROM NOW()) aa;
select EXTRACT(WEEK FROM NOW()) aa;

10、从数字数据获取时间

-- 从数字数据获取时间
SELECT FROM_DAYS(730669.89) aa;
SELECT FROM_UNIXTIME(1196440219.12) aa;
SELECT FROM_UNIXTIME(1196440219) + 0 aa;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

11、发挥格式化日期字符串

官方定义的几种格式：

Function Call	Result
GET_FORMAT(DATE,'USA')	'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')	'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')	'%Y-%m-%d'
GET_FORMAT(DATE,'EUR')	'%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL')	'%Y%m%d'
GET_FORMAT(DATETIME,'USA')	'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'JIS')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'EUR')	'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL')	'%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA')	'%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')	'%H:%i:%s'
GET_FORMAT(TIME,'ISO')	'%H:%i:%s'
GET_FORMAT(TIME,'EUR')	'%H.%i.%s'
GET_FORMAT(TIME,'INTERNAL')	'%H%i%s'

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'EUR'));
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA'));
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'ISO'));

12、LAST_DAY获取当前月份的最后一天

SELECT LAST_DAY('2014-02-05') aa;
SELECT LAST_DAY('2014-02-05 11:11:11') aa;
SELECT LAST_DAY(NOW()) aa;

13、创建日期、时间MAKEDATE,MAKETIME

MAKEDATE(year,dayofyear)

MAKETIME(hour,minute,second)

select MAKEDATE(2014,10) aa;
select MAKETIME(13,1,6) aa;

14、增加月份PERIOD_ADD(P,N)

Adds N months to period P (in the format YYMM or YYYYMM)

select PERIOD_ADD('201401',3) aa;
select PERIOD_ADD('1401',3) aa;

 

15、计算月份差 PERIOD_DIFF(P1,P2)

P1,P2 (in the format YYMM or YYYYMM)

SELECT PERIOD_DIFF(201402,201403) aa;
SELECT PERIOD_DIFF('201402','201403') aa;

16、秒换算时间SEC_TO_TIME(seconds)

TIME_TO_SEC(time)

select SEC_TO_TIME(60) aa;
select SEC_TO_TIME(3600) aa;
select TIME_TO_SEC(current_time) aa;
select TIME_TO_SEC('00:10:10') aa;

17、字符串转日期STR_TO_DATE(str,format)

 

-- 格式必须要匹配
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y') aa;
SELECT STR_TO_DATE('2014-10-10','%Y-%m-%D') aa;
SELECT STR_TO_DATE('2014-10-10','%d,%m,%Y') aa;

18、时间格式化 TIME_FORMAT(time,format)

select  TIME_FORMAT('08:08:01','%h:%i:%s') aa;
select  TIME_FORMAT(current_time,'%H:%m:%s') aa;

19、时间戳加减

TIMESTAMPADD(unit,interval,datetime_expr)

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

SELECT TIMESTAMPADD(YEAR,1,'2014-01-02') aa;
SELECT TIMESTAMPADD(MONTH,1,'2014-01-02') aa;
SELECT TIMESTAMPADD(DAY,1,'2014-01-02') aa;

SELECT TIMESTAMPDIFF(YEAR,NOW(),'2014-01-02') aa;
SELECT TIMESTAMPDIFF(MONTH,NOW(),'2014-01-02') aa;
SELECT TIMESTAMPDIFF(DAY,NOW(),'2014-01-02') aa;

20、UTC时间获取

select UTC_DATE() a1,UTC_TIME() a2,UTC_TIMESTAMP() a3;