MySQL对日期的操作

目录

DATEDIFF() 定义和用法

mysql获取日期语句汇总

---

DATEDIFF() 定义和用法

函数返回两个日期之间的时间。

语法：DATEDIFF(datepart,startdate,enddate)

select curdate();                       --获取当前日期
select last_day(curdate());                    --获取当月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day);   --获取本月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month); -- 获取下个月的第一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual; --获取当前月的天数
select DATEDIFF(curdate(),DATE_ADD(curdate(),interval -day(curdate()) day)) --当前日期是本月第几天

mysql获取日期语句汇总

-- 今天
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';

-- 昨天
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';

-- 上周
SELECT DATE_FORMAT(DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
SELECT DATE_FORMAT(SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';

-- 本周
SELECT DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
-- 上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT(DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';

-- 上月
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
SELECT DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';

-- 本月
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
SELECT DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';