MySQL按时间统计今天、昨天、本周、本月、上一月 、本季度、上季度、今年的数据

        以前导出都是加一个时间戳,然后进行where筛选,今天看到一个方法不错,效率应该差不多。省去了建立一个字段了。还不错,在这里记录一下,方便自己和其他人吧。

--今天
select * from 表名 where to_days(时间字段名) = to_days(now());
--昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
--本周
SELECT * FROM 表名 WHERE YEARWEEK( date_format( 时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ;
--本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' )
--上一个月
SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m')) =1
--本年
SELECT * FROM 表名 WHERE YEAR( 时间字段名 ) = YEAR( NOW( ) )
--上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
--查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
--查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
--查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
--查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
--查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
--查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
--查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
--查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
--查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select *
from user
where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
select *
from [ user ]
where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())
and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
select *
from [ user ]
where pudate between 上月最后一天
and 下月第一天
where date(regdate) = curdate();
select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())
SELECT date( c_instime ) ,curdate( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30
--今天
 
select * from 表名 where to_days(时间字段名) = to_days(now());
 
--昨天
 
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
 
--本周
 
SELECT * FROM  表名 WHERE YEARWEEK( date_format(  时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ;
 
--本月
 
SELECT * FROM  表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' ) 
 
--上一个月
 
SELECT * FROM  表名 WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m')) =1
 
--本年
 
SELECT * FROM 表名 WHERE YEAR(  时间字段名 ) = YEAR( NOW( ) ) 
 
 
--上一月
 
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
 
 
 
--查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
--查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
--查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
--查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
 
 
 
 
--查询当前这周的数据 
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
 
--查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
 
--查询当前月份的数据
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
 
--查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
 
--查询上个月的数据
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
 
select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;
 
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
 
select * 
from user 
where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
 
select * 
from [ user ] 
where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())
and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
 
select * 
from [ user ] 
where pudate between 上月最后一天
and 下月第一天
 
where   date(regdate)   =   curdate();
 
select   *   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   and   day(regdate)=day(now())
 
SELECT date( c_instime ) ,curdate( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30
--今天 select * from 表名 where to_days(时间字段名) = to_days(now()); --昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1 --本周 SELECT * FROM 表名 WHERE YEARWEEK( date_format( 时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ; --本月 SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' ) --上一个月 SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m')) =1 --本年 SELECT * FROM 表名 WHERE YEAR( 时间字段名 ) = YEAR( NOW( ) ) --上一月 SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1 --查询本季度数据 select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now()); --查询上季度数据 select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER)); --查询本年数据 select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW()); --查询上年数据 select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year)); --查询当前这周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now()); --查询上周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1; --查询当前月份的数据 select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m') --查询距离当前现在6个月的数据 select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now(); --查询上个月的数据 select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ; select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) select * from user where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now()) select * from [ user ] where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now()) and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now()) select * from [ user ] where pudate between 上月最后一天 and 下月第一天 where date(regdate) = curdate(); select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now()) SELECT date( c_instime ) ,curdate( ) FROM `t_score` WHERE 1 LIMIT 0 , 30

本文转载自阿龙爱吃肉博客,原文链接:https://3zi.cn/index.php/2020/12/28/mysql%e6%8c%89%e6%97%b6%e9%97%b4%e7%bb%9f%e8%ae%a1%e4%bb%8a%e5%a4%a9%e3%80%81%e6%98%a8%e5%a4%a9%e3%80%81%e6%9c%ac%e5%91%a8%e3%80%81%e6%9c%ac%e6%9c%88%e3%80%81%e4%b8%8a%e4%b8%80%e6%9c%88-%e3%80%81/

© 版权声明
THE END
喜欢就支持一下吧
点赞6 分享
Don't give up just because of what people said. Use that as your motivation to push harder.
别因为别人说的话而放弃,把那些话当做加倍努力的动力
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片快捷回复

    暂无评论内容