MySQL相应时间的数据查询

需求介绍

MySQL 查询今天、昨天、近7天、当前周、上周、近30天、本月、上一月、距离当前现在6个月、本季度、上季度、本年、上年

源码展示

-- 这里 sys_user 为表名  create_time 为查询的时间字段 
-- 查询今天
SELECT * FROM sys_user WHERE to_days( create_time ) = to_days(now());
-- 查询昨天
SELECT * FROM sys_user WHERE TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1;


-- 查询近7天
SELECT * FROM sys_user where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);
-- 这里要注意的问题是在MySQL中每周的周日是一周的第一天;所以如果是周日的话，它就回当作每周的第一天来查询了
-- 查询当前这周的数据 
SELECT * FROM sys_user WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());
-- 查询上周的数据
SELECT * FROM sys_user WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-1;


-- 查询近30天
SELECT * FROM sys_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time);
-- 查询本月
SELECT * FROM sys_user WHERE DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
-- 查询上一月
SELECT * FROM sys_user WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) = 1;
-- 查询距离当前现在6个月的数据
SELECT * FROM sys_user WHERE create_time between date_sub(now(),interval 6 month) and now();


-- 查询本季度数据
SELECT * FROM sys_user WHERE QUARTER(create_time) = QUARTER(now());
-- 季度函数：QUARTER(date) 函数返回给定日期值（1到4之间的数字）的一年中的季度;date必须项。从中提取季度的日期或日期时间
-- 查询上季度数据
-- IF(QUARTER (CURDATE()) = 1,YEAR (CURDATE()) - 1,YEAR (CURDATE())):如果当前季度为1季度,则年份减1
SELECT * FROM sys_user AS d WHERE YEAR ( d.create_time ) = IF(QUARTER (CURDATE()) = 1,YEAR (CURDATE()) - 1,YEAR (CURDATE()))AND QUARTER ( d.create_time )= QUARTER (DATE_SUB( CURDATE(), INTERVAL 1 QUARTER ));


-- 查询本年数据
SELECT * FROM sys_user WHERE YEAR(create_time) = YEAR(NOW());
-- 查询上年数据
SELECT * FROM sys_user WHERE YEAR(create_time) = YEAR(date_sub(now(),interval 1 year));