MySQL中常用到的关于时间的SQL

-- 今天

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS dayStart;
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS dayEnd;

 

-- 昨天

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS yesterdayStart;
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS yesterdayEnd;

 

-- 上周

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

 

 

-- 本周

SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS weekStart;
SELECT DATE_FORMAT(DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS weekEnd;

 

 

-- 上月

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

 

 

-- 本月

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AS monthStart;
SELECT DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS monthEnd;

 

 

--获取今天时间年月日

SELECT curdate() as nowDate

 

 

-- 获取现在时间(年月日时分秒)

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') as nowTime

 

 

--获取本月有多少天

select 
　　DATEDIFF(date_add(curdate() - day(curdate()) + 1, interval 1 month),
　　DATE_ADD(curdate(), interval -day(curdate()) + 1 day)) as day
from dual;

 

 

 获取某个月的每一天或当前月的每一天日期

此SQL参考自 https://blog.csdn.net/qq_38798251/article/details/89140471

SELECT date
FROM (select DATE_ADD(t1.dayed, INTERVAL t2.number DAY) date
      from (select DATE_ADD(curdate(), interval -day(curdate()) + 1 day) dayed) t1,
           (SELECT @xi := @xi + 1 as number
            from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
                 (SELECT 1
                  UNION
                  SELECT 2
                  UNION
                  SELECT 3
                  UNION
                  SELECT 4
                  UNION
                  SELECT 5
                  UNION
                  SELECT 6
                  UNION
                  SELECT 7
                  UNION
                  SELECT 8) xc2,
                 (SELECT @xi := -1) xc0) t2
      where DATE_ADD(t1.dayed, INTERVAL t2.number DAY)) time
WHERE SUBSTRING(date, 1, 7) = SUBSTRING(CURDATE(), 1, 7)

　　

 

 

另有一些未用到的SQL 做个记录

1、查询当前时间3天前的时间点

select date_sub(now() ,interval 3 day)

2.查询当前时间的时间3天之后的时间点

select date_sub(now(),interval -3 day)

3.查询当前时间3个月之前的时间点

select date_sub(now(),interval 3 month)

4.查询当前时间3个月之后的时间点

select date_sub(now,interval -3 month)