SQL语句 日期查询

-- 7天内
-- select * from bjslg_web.sjcj_ryjbxx ryxx where DateDiff(dd,ryxx.create_datetime,getdate())<=7

-- 7天内
SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(ryxx.create_datetime)

-- 本月
SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx WHERE DATE_FORMAT( ryxx.create_datetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

-- 上个月
SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( ryxx.create_datetime, '%Y%m' ) ) =1

-- 今天
select count(1) from bjslg_web.sjcj_ryjbxx ryxx where to_days(ryxx.create_datetime) = to_days(now());

-- 昨天
SELECT count(1) FROM bjslg_web.sjcj_ryjbxx ryxx WHERE TO_DAYS( NOW( ) ) - TO_DAYS( ryxx.create_datetime) <= 1

-- 本季度
select * from bjslg_web.sjcj_ryjbxx ryxx where QUARTER(ryxx.create_datetime)=QUARTER(now());  

-- 上季度
select * from bjslg_web.sjcj_ryjbxx ryxx where QUARTER(ryxx.create_datetime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  

-- 本年
select count(*) from bjslg_web.sjcj_ryjbxx ryxx where YEAR(ryxx.create_datetime)=YEAR(NOW());  

-- 上年
select count(*) from bjslg_web.sjcj_ryjbxx ryxx where year(ryxx.create_datetime)=year(date_sub(now(),interval 1 year));  

--  获取今天是一周第几天或星期几  0为周一，6为周日
SELECT WEEKDAY(now())

-- 获取本周的第一天日期
SELECT DATE_SUB(now(),INTERVAL WEEKDAY(now()) day) rq

-- 上周的第一天至现在的数据
-- SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx WHERE ryxx.create_datetime >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d')

– 7天内 不好使
– SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx WHERE YEARWEEK(date_format(ryxx.create_datetime,’%Y-%m-%d’)) =YEARWEEK(now())
– SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx where ryxx.create_datetime>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
SELECT * from bjslg_web.sjcj_ryjbxx ryxx
where ryxx.create_datetime between date_sub(now(),interval 1 week) and now() – 七天

-- 上周  好使
SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx WHERE YEARWEEK(date_format(ryxx.create_datetime,'%Y-%m-%d')) =YEARWEEK(now())-1

-- 距离当前6个月
select * from bjslg_web.sjcj_ryjbxx ryxx where ryxx.create_datetime between date_sub(now(),interval 6 month) and now();
-- SELECT * FROM bjslg_web.sjcj_ryjbxx ryxx WHERE ryxx.create_datetime>=(SELECT DATE_SUB(NOW(),INTERVAL 6 MONTH));
-- 三个月
select * from bjslg_web.sjcj_ryjbxx ryxx where ryxx.create_datetime>DATE_SUB(CURDATE(), INTERVAL 3 MONTH)       -- 

今天日期:	select CURDATE();-- 2021-02-01 类型

每个小时
SELECT HOUR
( e.create_datetime ) AS HOUR,
count( * ) AS Count
FROM
bjslg_web.sjcj_fwjbxx e
WHERE
DATE_FORMAT( e.create_datetime , ‘%Y-%m-%d’ )BETWEEN ‘2022-02-21’ AND ‘2022-02-21’
GROUP BY
HOUR ( e.create_datetime )
ORDER BY
HOUR ( e.create_datetime );