mysql 按时间段统计

按年汇总，统计

select sum(mymoney) as totalmoney, count(*) as sheets from mytable groupby date_format(col, '%Y');

 

按月汇总，统计

select sum(mymoney) as totalmoney, count(*) as sheets from mytable groupby date_format(col, '%Y-%m');

 

按季度汇总，统计

select sum(mymoney) as totalmoney,count(*) as sheets from mytable groupby concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

 

按小时

select sum(mymoney) as totalmoney,count(*) as sheets from mytable groupby 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()) andyear(my_time2) = year(curdate())

 

本周统计

select * from mytable where month(my_time1) = month(curdate()) andweek(my_time2) = week(curdate())

 

N天内记录

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