整理MySql使用过程中与日期时间相关常用的函数和用法。
日期单位：SECOND（秒） MINUTE（分） HOUR（时） DAY（日） WEEK（周） MONTH（月） QUARTER（季） YEAR（年）
一、当前日期时间
-- 当前日期函数变量
SELECT CURRENT_DATE;-- 变量
SELECT CURDATE();-- 函数
-- 当前时间函数变量
SELECT CURRENT_TIME;-- 变量
SELECT CURTIME();-- 函数
-- 当前时间时间变量
SELECT CURRENT_TIMESTAMP;-- 变量
-- 当前日期时间函数
SELECT CURRENT_TIMESTAMP();-- 函数
-- 当前系统日期时间
SELECT NOW();-- 执行时获取
SELECT SYSDATE();-- 执行中获取
二、日期加 DATE_ADD
格式：DATE_ADD(objDate,INTERVAL X UNION)，objDate 为一个日期，INTERVAL 为关键字，X 为数值（正加负减） UNION 为日期单位。
注意：X为负的时候DATE_ADD=DATE_SUB两个函数正好相反。UNION单位可到时间层级。
-- 当前加一天
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 1 DAY);
-- 当前减一天
SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY);
-- 当前加一周
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 1 WEEK);
-- 当前减一周
SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 WEEK);
-- 当前加一月
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 1 MONTH);
-- 当前减一月
SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH);
-- 当前加一季
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 1 QUARTER);
-- 当前减一季
SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 QUARTER);
-- 当前加一年
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 1 YEAR);
-- 当前减一年
SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 YEAR);
-- 也可用于到时间层级
SELECT DATE_ADD(NOW(),INTERVAL 1 SECOND);
SELECT DATE_ADD(NOW(),INTERVAL 1 MINUTE);
SELECT DATE_ADD(NOW(),INTERVAL 1 HOUR);
三、日期减 DATE_SUB
格式：DATE_ADD(objDate,INTERVAL X UNION)，objDate 为一个日期，INTERVAL 为关键字，X 为数值（正减负加） UNION 为日期单位。
注意：X为负的时候DATE_SUB=DATE_ADD两个函数正好相反。UNION单位可到时间层级。至于使用跟DATE_ADD一样，一般使用DATE_ADD足矣。
-- 当前减一天
SELECT DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY);
-- 当前加一天
SELECT DATE_SUB(CURRENT_DATE,INTERVAL -1 DAY);
四、日期差 DATEDIFF
格式：DATEDIFF(dateA,dateB) dateA与dateB相差天数，dateA>bateB结果大于0，dateA<dateB,结果小于0，dateA=dateB,结果等于0
SELECT DATEDIFF(CURRENT_DATE,'2020-02-29');-- 大于0
SELECT DATEDIFF('2020-02-29',CURRENT_DATE);-- 小于0
SELECT DATEDIFF(CURRENT_DATE,CURRENT_DATE);-- 等于0
五、时间差 TIMEDIFF
格式：DATEDIFF(timeA,timeB) timeA与timeB相差的时间，返回时分秒时间格式。
-- 返回时间格式，即相差多少小时多少分多少秒
SELECT TIMEDIFF(CURRENT_TIME,'00:01:00');
SELECT TIMEDIFF(NOW(),NOW());
六、指定单位日期时间差 TIMESTAMPDIFF
格式：TIMESTAMPDIFF(UNION,dateTime1,dateTime2) 返回dateTime1与dateTime相差单位的数值，从dateTime1到dateTime2相差的单位数值，x=dateTime2-dateTime1，UNION 日期单位的值如下：
SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
SELECT TIMESTAMPDIFF(SECOND,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(MINUTE,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(HOUR,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(DAY,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(WEEK,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(MONTH,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(QUARTER,'2019-01-01 11:11:11',NOW());
SELECT TIMESTAMPDIFF(YEAR,'2019-01-01 11:11:11',NOW());
七、日期格式化 DATE_FORMAT
格式：DATE_FORMAT(date,formatStr)。
注意：formatStr所到的层级date需要有值，否则为0；date可以是字符串和日期类型。
SELECT DATE_FORMAT(NOW(),'%Y');-- 年层级
SELECT DATE_FORMAT(NOW(),'%Y-%m');-- 年月层级
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');-- 年月日期层级
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');-- 年月日时分秒层级
SELECT DATE_FORMAT(NOW(),'%H');-- 时层级
SELECT DATE_FORMAT(NOW(),'%H:%i');-- 时分层级
SELECT DATE_FORMAT(NOW(),'%H:%i:%s');-- 时分秒层级
八、时间戳日期互转
1、日期转为时间戳 UNIX_TIMESTAMP
返回十位长度的时间戳数字
SELECT UNIX_TIMESTAMP(CURRENT_DATE);
SELECT UNIX_TIMESTAMP(NOW());
2、时间戳转为日期 FROM_UNIXTIME
十位长度的时间戳数字或字符串转为对应的日期
SELECT FROM_UNIXTIME('1592928000');
SELECT FROM_UNIXTIME('1593003664');
九、字符串转为日期 STR_TO_DATE
格式：STR_TO_DATE(objDateTimeStr,formatStr)
注意：formatStr所到的层级objDateTimeStr也须到，否则为0
-- 目标字符串时间层级没有默认为 00:00:00
SELECT STR_TO_DATE('2020-02-01','%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('2020-06-24 21:03:06','%Y-%m-%d %H:%i:%s');
十、其他一些函数
- EXTRACT 获取日期中的目标
SELECT EXTRACT(SECOND FROM NOW());-- 获取时间所属的秒
SELECT EXTRACT(MINUTE FROM NOW());-- 获取时间所属的分
SELECT EXTRACT(HOUR FROM NOW());-- 获取时间所属的时
SELECT EXTRACT(DAY FROM NOW());-- 获取时间所属的日
SELECT EXTRACT(WEEK FROM NOW());-- 获取时间所属的周
SELECT EXTRACT(QUARTER FROM NOW());-- 获取时间所属的季
SELECT EXTRACT(YEAR FROM NOW());-- 获取时间所属的年
- LAST_DAY 获取当前日期所属月的最后一天
SELECT LAST_DAY('2020-02-01');
- MAKEDATE 某天开始多少天后的日期
SELECT MAKEDATE(2020,31); -- '2020-01-31' day of year
- MAKETIME 时分秒组成一个日期
SELECT MAKETIME(12,15,30); -- '12:15:30' hour minuts seccond
- TIME_TO_SEC 时间转化成秒
-- 时间转为秒
SELECT TIME_TO_SEC('01:20:15'); -- 4815 秒
-- 求两个时间相差多少秒
SELECT TIME_TO_SEC('01:30:15')-TIME_TO_SEC('01:20:15');-- 600
SELECT TIME_TO_SEC(TIMEDIFF('01:30:15','01:20:15'));-- 600
- SEC_TO_TIME 秒转化成时间
SELECT SEC_TO_TIME(362);-- 6分2秒
- TO_DAYS 目标日期幻化成天数，从0000-00-00开始 不常用
SELECT TO_DAYS(CURRENT_DATE);
- FROM_DAYS 目标天数幻化成日期，从0000-00-00开始 不常用
SELECT FROM_DAYS(1);
SELECT FROM_DAYS(360);
SELECT FROM_DAYS(365);
SELECT FROM_DAYS(366);
SELECT FROM_DAYS(737965);
- CONVERT_TZ 时区转换
格式：CONVERT_TZ(objDateTimeStr,fromTimeZone,toTimeZone)
-- 08时区转到00时区，即8个小时前
SELECT CONVERT_TZ('2020-01-01 12:00:00','+08:00','+00:00');
-- 这种几个小时前后等也可以用 DATE_ADD和DATE_SUB来实现
-- 8个小时前
SELECT DATE_SUB('2020-01-01 12:00:00',INTERVAL 8 HOUR);
SELECT DATE_ADD('2020-01-01 12:00:00',INTERVAL -8 HOUR);
各函数之间搭配使用能达到意想不到的效果
网友评论