MySQL----日期相关

获取当前日期
select curdate();
结果： 2023-04-27

获取当前日期为几号
select day(curdate())
结果：27

在当前日期上加上时间间隔
select DATE_ADD(curdate(),interval 2 day)

结果：2023-04-29

一、获取本月第一天

select DATE_ADD(curdate(),interval -day(curdate())+1 day) -- 获取本月第一天

结果：2023-04-01

二、获取本月最后一天

select last_day(curdate()); -- 获取当月最后一天

结果：2023-04-30

三、获取下月第一天

select date_add(curdate()-day(curdate())+1,interval 1 month ) -- 获取下个月的第一天

结果：2023-05-01

四、获取当月的天数

select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual -- 获取当前月的天数

select day(date_add( date_add(curdate(),interval 1 month),interval -day(curdate()) day ))

五、明天日期：

select DATE_SUB(curdate(),INTERVAL -1 DAY) AS tomorrow

结果：2023-04-28

六、本周日期：

SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < 6 - WEEKDAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
WHERE @s > 0
ORDER BY `date`

结果：

 

 

查询当月第一天和最后一天：

select DATE_FORMAT(now(),'%Y-%m-01') as first_day_of_month,
       LAST_DAY(now()) as last_day_of_month

 

查询上月第一天和最后一天

select DATE_FORMAT(DATE_ADD(now(),INTERVAL -1 month),'%Y-%m-01')
            as first_day_of_last_month,
            LAST_DAY(DATE_ADD(now(),INTERVAL -1 month))
            as last_day_of_last_month

 

查询下月第一天和最后一天

select DATE_FORMAT(DATE_ADD(now(),INTERVAL 1 month),'%Y-%m-01')
            as first_day_of_last_month,
            LAST_DAY(DATE_ADD(now(),INTERVAL 1 month))
            as last_day_of_last_month

当周的每天日期，从第周天开始为第一天

select DATE_ADD(CURDATE(),INTERVAL 0-DAYOFWEEK(CURDATE()) day) as 
first_day,DATE_ADD(CURDATE(),INTERVAL 6-DAYOFWEEK(CURDATE()) day) as 
end_day,DATE_ADD(CURDATE(),INTERVAL 0-DAYOFWEEK(CURDATE()) day) as 
sunday,DATE_ADD(CURDATE(),INTERVAL 1-DAYOFWEEK(CURDATE()) day) as 
monday,DATE_ADD(CURDATE(),INTERVAL 2-DAYOFWEEK(CURDATE()) day) as 
tuesday,DATE_ADD(CURDATE(),INTERVAL 3-DAYOFWEEK(CURDATE()) day) as 
wednesday,DATE_ADD(CURDATE(),INTERVAL 4-DAYOFWEEK(CURDATE()) day) as 
thursday,DATE_ADD(CURDATE(),INTERVAL 5-DAYOFWEEK(CURDATE()) day) as 
friday,DATE_ADD(CURDATE(),INTERVAL 6-DAYOFWEEK(CURDATE()) day) as 
saturday