mysql 按时间段（年，季度，月，天，时）统计数据

DATE_FORMAT() 函数

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法：

DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。

DATE_FORMAT实例

1.把字符串转为日期格式

实例：

SELECT DATE_FORMAT(‘2017-09-20 08:30:45’, ‘%Y-%m-%d %H:%i:%S’);

输出结果：

2017-09-20 08:30:45（日期Date格式）

2.把日期转为字符串格式

实例：

SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’);

输出结果：

2017-04-05 16:53:59（String型格式）

按年汇总，统计

SELECT sum(mymoney) as totalmoney, count(*) as sheets FROM mytable 
GROUP BY date_format(col, '%Y');

按月汇总，统计

SELECT sum(mymoney) as totalmoney, count(*) as sheets FROM mytable 
GROUP BY date_format(col, '%Y-%m');

按季度汇总，统计

SELECT sum(mymoney) as totalmoney,count(*) as sheets FROM mytable 
GROUP BY concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

按天统计数据

数据库中的日期为精确到秒的，想按照天统计，每一天的数据，sql语句如下

原有的查询出来的结果是每天每小时每秒的数据统计

SELECT read_time,sum(read_number) as read_total FROM `mdz_activity_share` where ai_id=340 and read_number > 0  GROUP BY 
 read_time asc  

优化后：

SELECT date_format(read_time, '%Y-%m-%d'),sum(read_number) as read_total FROM `mdz_activity_share` where ai_id=340 and read_number > 0  GROUP BY  date_format(read_time, '%Y-%m-%d') asc 

打印结果

按小时统计

SELECT sum(mymoney) as totalmoney,count(*) as sheets FROM mytable 
GROUP BY date_format(col, '%Y-%m-%d %H ');

查询 本年度的数据:

SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

查询数据附带季度数:

SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;

查询 本季度的数据:

SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

查询 本月统计:

SELECT * FROM mytable 
where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

查询 本周统计:

SELECT * FROM mytable 
where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

查询 N天内记录:

WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N