1. 时间差函数(TIMESTAMPDIFF)
统计考核指标时,需要计算差错影响期数,需使用TIMESTAMPDIFFF计算时间差,预研如下
select TIMESTAMPDIFF(MONTH, '2019-04-24 23:59:00', '2019-04-26 00:00:00');
结果为:0
select TIMESTAMPDIFF(DAY, '2019-04-24 00:00:00', '2019-04-26 00:00:00');
结果为:2
select TIMESTAMPDIFF(DAY, '2019-04-24 00:00:00', '2019-04-26 12:00:00');
结果为:2
select TIMESTAMPDIFF(HOUR, '2019-04-24 00:00:00', '2019-04-24 23:00:00');
结果为:23
select TIMESTAMPDIFF(MINUTE, '2019-04-24 00:00:00', '2019-04-24 23:00:00');
结果为:1380
2. 日期转换计算函数(date_add、date_sub)
-- 当前月的最后一天
select last_day(now());
-- 下月第一天
select date_add(last_day(now()), interval 1 day);
-- 当天为当月的第几天
select day(now());
-- 当月第一天
select date_add(now(), interval 1-(day(curdate())) day);
--当前日期的上一个月
DATE_SUB(now(),INTERVAL 1 MONTH)
--获取年,月,日
year(now())
month(now())
day(now())