MySQL时间统计

来源于互联网，收集整理

时间戳格式

SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks; 

SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days; 

SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months 

DATE格式

#按年汇总，统计：
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));
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

#按小时：
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