记录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) >=#{params.beginTime}
</if>/*按照年度查询*/
<if test="params.selected == 'y'.toString() and params.endTime != null and params.endTime != '' ">
AND Year(create_time) <= #{params.endTime}
</if>/**结束年度查询*/
<if test="params.selected == 's'.toString() and params.beginTime != null and params.beginTime != '' ">
AND concat(Year(create_time),quarter(create_time)) >= 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)) <= 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') >=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') <= 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') >= 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') <= date_format(#{params.endTime},'%y%m%d')
</if> <!-- 结束时间检索 (结束天查询)-->
更多学习可以参照
https://blog.csdn.net/weixin_44167913/article/details/94436378