PostgreSQL实现按年、月、日、周、时、分、秒的分组统计

按年查询

select to_char(date::DATE, "YYYY") as year,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= "2019-01-01" and  date <= "2020-01-01"  group by year order by year

按月查询

select to_char(date::DATE, "YYYY-MM") as month,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= "2019-01-01" and  date <= "2020-01-01"  group by month order by month

按周查询

select to_char(date::DATE-(extract(dow from date::TIMESTAMP)-1||"day")::interval, "YYYY-mm-dd") week,
sum(shares) as shares, sum(visits) as visits 
from database_table 
where date >= "2019-01-01" and  date <= "2020-01-01" group by week order by week

按天查询

select to_char(date::DATE, "YYYY-MM-DD") as day,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= "2019-01-01" and  date <= "2020-01-01"  group by day order by day

按小时查询

select to_char(date::DATE, "YYYY-MM-DD  HH24") as hour,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= "2019-01-01" and  date <= "2020-01-01"  group by hour order by hour

按分钟查询

select to_char(date::DATE, "YYYY-MM-DD  HH24:MI ") as minute,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= "2019-01-01" and  date <= "2020-01-01"  group by minute order by minute

按秒查询

select to_char(date::DATE, "YYYY-MM-DD  HH24:MI:SS ") as second,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= "2019-01-01" and  date <= "2020-01-01"  group by second order by second

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
 

原文地址：https://www.cnblogs.com/chenyablog/p/12482855.html

查看更多关于PostgreSQL实现按年、月、日、周、时、分、秒的分组统计的详细内容...