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 data_sub(now(),interval -3 day)

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

select data_sub(now(),interval 3 month)

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

select data_sub(now,interval -3 month)