MySql: Year, Quarter, Month, Day, Hour statistics

 

 

--  统计

select count(*) as '当天记录数' from web_product where date(p_createtime) = curdate(); 
select count(*) as '当天记录数' from web_product where to_days(p_createtime) = to_days(now());

select count(*) as '昨天记录数'  from web_product where to_days( now( ) ) - to_days( p_createtime) <= 1;


-- 前一天
select count(*) as '前一天记录数' from web_product where date(p_createtime) = date_sub(curdate(),interval 1 day);

select count(*) as '本周记录数' from web_product where date(p_createtime) >= date_sub(curdate(),interval 7 day)    
and date(p_createtime) <=  date_sub(curdate(),interval 1 day);

select count(*) as '7天的记录数' from web_product where date_sub(curdate(), interval 7 day) <= date(p_createtime);

-- 查询近30天的记录
select * from web_product where date_sub(curdate(), interval 30 day) <= date(p_createtime);

-- 查询本月的记录
select * from web_product where date_format(p_createtime,'%y%m')=date_format(curdate(),'%y%m');


-- 查询上一月的记录
select * from web_product where period_diff(date_format(now(),'%y%m'),date_format(p_createtime,'%y%m'))=1;


-- 查询本季度数据
select * from web_product where quarter(p_createtime)=quarter(now());

-- 查询上季度数据
select * from web_product where quarter(p_createtime)=quarter(date_sub(now(),interval 1 quarter));

-- 查询本年数据
select * from web_product  where year(p_createtime)=year(now());

-- 查询上年数据
select * from web_product where year(p_createtime)=year(date_sub(now(),interval 1 year));


-- 查询当前这周的数据 
select * from web_product where yearweek(date_format(p_createtime,'%y-%m-%d')) = yearweek(now());

-- 查询上周的数据
select * from web_product where yearweek(date_format(p_createtime,'%y-%m-%d')) = yearweek(now())-1;

-- 查询当前月份的数据
select * from web_product   where date_format(p_createtime,'%y-%m')=date_format(now(),'%y-%m');
-- 查询距离当前现在6个月的数据
select p_name,p_createtime from web_product where p_createtime between date_sub(now(),interval 6 month) and now();

-- 按年汇总，统计：

select sum(mymoney) as totalmoney, count(*) as sheets from web_product group by date_format(p_createtime, '%y');

select date_format(p_createtime, '%y') as 'year',count(*) as sheets from web_product group by date_format(p_createtime, '%y');


select date_format(p_createtime,'%y') years,sum(duration) dur from web_product tv where 1=1 group by years order by years desc;

select date_format(p_createtime,'%y') years,count(*) as sheets  from web_product  where 1=1 group by years order by years desc;

select date_format(p_createtime,'%y') years,count(*) count from web_product group by years;


select year(p_createtime) as 'yearname',count(*) as'sheet' from `web_product` group by  yearname;

select count(*), year(p_createtime) yearname  from `web_product`  group by  yearname;

select year(p_createtime) yearname  from `web_product`;

select  distinct(year(p_createtime)) yearname  from `web_product`;

select  count(distinct(year(p_createtime))) yearname  from `web_product`;


select year(addtime) as 'yearname',count(*) as'sheet' from `duwebstat` group by  yearname;

select  count(distinct(year(addtime))) yearname  from `duwebstat`;

-- 按月汇总，统计：

select sum(mymoney) as totalmoney, count(*) as sheets from web_product group by date_format(p_createtime, '%y-%m');

select date_format(p_createtime, '%y-%m') as 'month',count(*) as sheets from web_product group by date_format(p_createtime, '%y-%m');

select date_format(p_createtime,'%y%m') months,count(*) as sheets from web_product  where 1=1 group by months order by months desc;

select date_format(p_createtime,'%y%m') months,count(*) count from web_product group by months;

select year(p_createtime) as 'yearname',month(`p_createtime`) as 'monthname',count(*) as'sheet' from `web_product` group by  yearname,monthname;

select year(addtime) as 'yearname',month(`addtime`) as 'monthname',count(*) as'sheet' from `duwebstat` group by  yearname,monthname;

select  count(distinct(concat(cast(year(addtime) as char(50)),cast(month(addtime) as char(50)))))  from duwebstat;

select date_format(addtime,'%y-%m') months,count(*) as sheets from duwebstat  where 1=1 group by months order by months desc;


-- 按季度汇总，统计：

select sum(mymoney) as totalmoney,count(*) as sheets from web_product group by concat(date_format(p_createtime, '%y'),floor((date_format(p_createtime, '%m')+2)/3));

select count(*) as sheets from web_product group by concat(date_format(p_createtime, '%y'),floor((date_format(p_createtime, '%m')+2)/3));

select concat(date_format(p_createtime,'%y'),floor((date_format(p_createtime, '%m')+2)/3)) quarters,sum(duration) dur from web_product  where 1=1  group by quarters order by quarters desc;

select concat(date_format(p_createtime,'%y'),floor((date_format(p_createtime, '%m')+2)/3)) quarters,count(*) as sheets  from web_product  where 1=1  group by quarters order by quarters desc;


select id, year(p_createtime),quarter(`p_createtime`) from `web_product`;

select year(p_createtime) as 'yearname',quarter(`p_createtime`) as 'quartername',count(*) as'sheet' from `web_product` group by  yearname,quartername;

select  distinct(concat(cast(year(p_createtime) as char(50)),cast(quarter(p_createtime) as char(50))))  from web_product;

select  count(distinct(concat(cast(year(p_createtime) as char(50)),cast(quarter(p_createtime) as char(50)))))  from web_product;


select cast(122 as char);

select now();

select quarter(now());

select cast(123 as char); 

select concat(date_format(now(),'%y'),cast(quarter(now()) as char(20)));



select year(addtime) as 'yearname',quarter(`addtime`) as 'quartername',count(*) as'sheet' from `duwebstat` group by  yearname,quartername;


select  count(distinct(year(addtime))) yearname  from `duwebstat`;


select  count(distinct(concat(cast(year(addtime) as char(50)),cast(quarter(addtime) as char(50)))))  from duwebstat;

--  按周统计
select date_format(p_createtime,'%y%u') weeks,count(*) as sheets from web_product where 1=1 group by weeks order by weeks desc;

select date_format(p_createtime,'%y-%u') weeks,count(*) as sheets from web_product where 1=1 group by weeks order by weeks desc;

select date_format(p_createtime,'%y%u') weeks,count(*) count from web_product group by weeks;

select date_format(addtime,'%y-%u') weeks,count(*) count from duwebstat group by weeks;


select distinct(date_format(p_createtime,'%y-%u')) from web_product;

select year(p_createtime) yearname,week(p_createtime) weeks,count(*) count from web_product group by weeks,yearname;

select year(addtime) yearname,week(addtime) weeks,count(*) count from duwebstat group by weeks,yearname;

select date_format(addtime,'%y%u') weeks,count(*) as sheets from duwebstat where 1=1 group by weeks order by weeks desc;

select count(distinct(date_format(addtime,'%y-%u'))) from duwebstat;

-- 按日统计
-- https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

select date_format(p_createtime,'%y%m%d') days,count(*) count from web_product group by days; 

select date_format(p_createtime,'%y-%m-%d') days,count(*) count from web_product group by days; 

select date_format(addtime,'%y-%m-%d') days,count(*) as sheet from duwebstat group by days;

select count(distinct(date_format(addtime,'%y-%m-%d'))) from duwebstat;


--
select date_format('2009-10-04 22:23:00', '%w %m %y');


-- 
-- 按小时：hour

select date_format(p_createtime, '%y-%m-%d %h'),count(*) as sheets from web_product group by date_format(p_createtime, '%y-%m-%d %h');

select date_format(p_createtime, '%y-%m-%d %h'),count(*) as sheets from web_product group by date_format(p_createtime, '%y-%m-%d %h') limit 0,30;


select date_format(addtime, '%y-%m-%d %h') as hours,count(*) as sheet from duwebstat group by date_format(addtime, '%y-%m-%d %h');


select sum(mymoney) as totalmoney,count(*) as sheets from web_product group by date_format(p_createtime, '%y-%m-%d %h ');

-- 查询 本年度的数据:

select * from web_product where year(from_unixtime(p_createtime)) = year(curdate());

-- 查询数据附带季度数:

select id, quarter(from_unixtime(p_createtime)) from web_product;

-- 查询 本季度的数据:

select * from web_product where quarter(from_unixtime(p_createtime)) = quarter(curdate());

-- 本月统计:

select * from web_product where month(p_createtime) = month(curdate()) and year(p_createtime) = year(curdate());

-- 本周统计:

select * from web_product where month(p_createtime) = month(curdate()) and week(p_createtime) = week(curdate());