MySQL 5.6常用日期函数_mysql 5.6　current_date()

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;

官方文档时间间隔定义：

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)

官方定义参数：

-- 日期格式化
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、发挥格式化日期字符串

官方定义的几种格式：

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;