2013.9.29 数据库SQL：mysql 时间函数在项目中的使用

最近项目中要到按每个月数据统计或者每天数据统计的统计结果，研究了一下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) /从给定时间上减去多少时间

SELECT * FROM policemenlogin WHERE logindate>DATE_SUB(NOW(),INTERVAL 1 DAY);

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/DAY

SELECT 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)