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
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持PHP之友。
您可能感兴趣的文章:- Postgresql 动态统计某一列的某一值出现的次数实例
- postgreSql分组统计数据的实现代码
- postgresql数据库使用说明_实现时间范围查询
- postgresql 实现查询某时间区间的所有日期案例
- postgresql 计算时间差的秒数、天数实例
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。