MySQL根据日期统计数据和查询数据
一、前言
- MySQL 5.6
- 参考:
二、正文
示例内容说明:表名 = demo ;时间字段 = create_time
1.统计数据
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
- 日统计
select DATE_FORMAT(t.create_time, '%Y%m%d') days
from demo t
group by days;
- 周统计
select DATE_FORMAT(t.create_time, '%Y%u') weeks
from demo t
group by weeks;
- 月统计
select DATE_FORMAT(t.create_time, '%Y%m') months
from demo t
group by months;
- 年统计
select DATE_FORMAT(t.create_time, '%Y') years
from demo t
group by years;
2.查询数据
- 查询今天数据
SELECT * FROM demo t
WHERE TO_DAYS(t.create_time) = TO_DAYS(NOW());
- 查询昨天数据
SELECT * FROM demo t
WHERE TO_DAYS(NOW()) - TO_DAYS(t.create_time) = 1;
- 近5天
SELECT * FROM demo t
WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= DATE(t.create_time)
- 截止当前时间,1个月的数据
SELECT * FROM demo t
WHERE BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) and NOW();
- 本周数据(将周一设置为每周的起点,默认周日)
SELECT * FROM demo t
WHERE YEARWEEK(DATE_FORMAT(t.create_time,'%Y-%m-%d'), 1) = YEARWEEK(NOW(), 1);
- 上周数据(将周一设置为每周的起点,默认周日)
SELECT * FROM demo t
WHERE YEARWEEK(DATE_FORMAT(t.create_time,'%Y-%m-%d'), 1) = YEARWEEK(NOW(), 1) -1;
- 本月数据
SELECT * FROM demo t
WHERE DATE_FORMAT(t.create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m');
- 上月数据
SELECT * FROM demo t
WHERE DATE_FORMAT(t.create_time,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m');