记录mysql中如何统计日周月季度年

记录mysql中如何统计日周月季度年

天天记录一小点，天天进步一小点

mysql中日期格式化方法

#今天
SELECT count(0) FROM pm_record WHERE TO_DAYS(date) = TO_DAYS(NOW());
#本周
SELECT count(0) FROM pm_record WHERE YEARWEEK(DATE_FORMAT(date,'%Y-%m-%d')) = YEARWEEK(NOW());
#本月
SELECT count(0) FROM pm_record WHERE DATE_FORMAT(date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
##本季度
SELECT count(0) FROM pm_record WHERE QUARTER(date) = QUARTER(NOW());
#本年
SELECT count(0) FROM pm_record WHERE YEAR(date)=YEAR(NOW());

这里查询本周有个坑，他默认是按照西方计算本周（从周日开始计算），中国的习惯是从周一开始计算

MySQL的YEARWEEK函数以及查询本周数据

MySQL 的 YEARWEEK 是获取年份和周数的一个函数，函数形式为 YEARWEEK(date[,mode])

例如 2010-3-14 ，礼拜天

SELECT YEARWEEK(‘2010-3-14’) 返回 11
SELECT YEARWEEK(‘2010-3-14’,1) 返回 10

其中第二个参数是 mode ，具体指的意思如下：

Mode	First day of week	Range	Week 1 is the first week
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with more than 3 days this year
2	Sunday	0-53	with a Sunday in this year
3	Monday	0-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	0-53	with more than 3 days this year
7	Monday	0-53	with a Monday in this year

按中国习惯本周统计：
SELECT * FROM table WHERE YEARWEEK(date_format(date,'%Y-%m-%d')) = YEARWEEK(now(),1);

		<if test="params.selected == 'y'.toString() and params.beginTime != null and params.beginTime != '' ">
            AND Year(create_time) &gt;=#{params.beginTime}
        </if>/*按照年度查询*/
        <if test="params.selected == 'y'.toString() and params.endTime != null and params.endTime != '' ">
            AND Year(create_time) &lt;= #{params.endTime}
        </if>/**结束年度查询*/
        <if test="params.selected == 's'.toString() and params.beginTime != null and params.beginTime != '' ">
            AND concat(Year(create_time),quarter(create_time)) &gt;= concat(Year(#{params.beginTime}),day(#{params.beginTime}))
        </if>/**按照季度查询*/
        <if test="params.selected == 's'.toString() and params.endTime != null and params.endTime != '' ">
            AND concat(Year(create_time),quarter(create_time)) &lt;= concat(Year(#{params.endTime}),day(#{params.endTime}))
        </if> /**结束季度查询*/
        <if test="params.selected == 'm'.toString() and params.beginTime != null and params.beginTime != '' ">
            AND date_format(create_time,'%y%m') &gt;=date_format(#{params.beginTime},'%y%m')
        </if>/**按照月份查询*/
        <if test="params.selected == 'm'.toString() and params.endTime != null and params.endTime != '' ">
            AND date_format(create_time,'%y%m') &lt;= date_format(#{params.endTime},'%y%m')
        </if>  /**结束月份查询*/
        <if test="params.selected == 'd'.toString() and params.beginTime != null and params.beginTime != '' ">
            AND date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
        </if><!-- 开始时间检索 (按照天查询)-->
        <if test="params.selected == 'd'.toString() and params.endTime != null and params.endTime != '' ">
            AND date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
        </if>  <!-- 结束时间检索 (结束天查询)-->

更多学习可以参照

https://blog.csdn.net/weixin_44167913/article/details/94436378