最近项目中要到按每个月数据统计或者每天数据统计的统计结果,研究了一下MYSQL中时间函数。
MYSQL:时间函数
1.查过去24小时之内的数据:checkDate >DATE_ADD(now(), INTERVAL -1 DAY)String sql = "select * from records where vehicleId='" + vehicleId
+ "' and checkDate >DATE_ADD(now(), INTERVAL -1 DAY)"
+ " order by checkDate DESC";
NOW()系统函数,指当前时间;
DATE_ADD(now(),INTERVAL -1 DAY);系统函数,指当前时间减去1天,即过去24小时时间段内
DATE_SUB(date,INTERVAL NUM UNIT) /从给定时间上减去多少时间
2.按天数查询数据
TO_DAYS(date)返回一个天数 (从年份0开始的天数) 类似于:SELECT DATEDIFF(now(),'20120112')/返回指定时间间的天数
SELECT COUNT(*),records.checkdate FROM records GROUP BY TO_DAYS(records.checkdate);
3.按月份查询数据
MONTH(date)/返回日期的月份,类似的还有/ YEAR/ DAYSELECT COUNT(*),records.checkdate FROM records GROUP BY MONTH(records.checkdate);
这里需要注意:如果有两条数据checkdate字段分别为2012-09-29和2013-09-29,查询出的结果数是2,因为这两条数据都是9月份的;
4.那要如何才能按月份查询出结果呢?
SELECT COUNT(*),records.checkdate FROM records GROUP BY PERIOD_DIFF(DATE_FORMAT(records.checkdate,'%Y%m'),'200001')
首先,PERIOD_DIFF(date,'200001')// date也必须是yyyyMM的格式 这里返回2000年01月到checkdate的月份数
EXTRACT(YEAR_MONTH FROM(records.checkdate))或者
DATE_FORMAT(date,'%Y%m')//注意大小写,有区别
// 这里将date转换为字符串yyyyMM格式,
最后使用group by 按月份分组即可筛选出结果:GROUP BY PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM(records.checkdate)),'200001')
5.获取当天的数据
SELECT * FROM records WHERE DATE_FORMAT(records.checkdate,'%Y%m%d')=CURDATE()//取当天数据
或者SELECT * FROM records WHERE TO_DAYS(records.checkdate)=TO_DAYS(NOW())
CURDATE()/返回当前日期(2013-09-29),NOW()/返回当前日期和时间 (2012-09-29 22:22:22)
6.按照周查询做数据统计
重点:YEARWEEK方法
SELECT COUNT(*), YEAR (pr.create_time), pr.create_time, YEARWEEK(pr.create_time) FROM payment_records pr WHERE YEAR (pr.create_time) = '2014' GROUP BY YEARWEEK(pr.create_time)