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