PostgreSQL 实现按月按年，按日统计 分组统计

select  to_char(time_field, 'YYYY-MM-DD  HH24:MI:SS ' ) as d ,  count(id)  as  total_countl,sum (count_field)   as  total_amount from  table_name
  where  time_field  between start_time  and  end_time  group by d

原文：https://blog.csdn.net/aleefang/article/details/78679402

 

按年分组查看

select  to_char(time_field, 'YYYY') as d ,  count(id)  as  total_count,sum (count_field)  as  total_amount from  table_name
  where  time_field  between  start_time  and  end_time group by d

 

按月分组查看

select  to_char(time_field, 'YYYY-MM') as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name
  where time_field between  start_time  and  end_time  group by d

按天分组查看

select  to_char(time_field, 'YYYY-MM-DD') as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name
  where time_field between  start_time  and  end_time  group by d

按小时分组查看

 select  to_char(time_field, 'YYYY-MM-DD  HH24 ' ) as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name
  where time_field  between start_time  and  end_time  group by d  order  by  d

按秒分组查看

select  to_char(time_field, 'YYYY-MM-DD  HH24:MI:SS ' ) as d ,  count(id)  as  total_countl,sum (count_field)   as  total_amount from  table_name
  where  time_field  between start_time  and  end_time  group by d