Postgresql按天、周、月、年统计信息

最新推荐文章于 2024-07-03 11:45:18 发布

cxylikui

最新推荐文章于 2024-07-03 11:45:18 发布

阅读量2k

点赞数 1

分类专栏： postgresql 文章标签： postgresql

本文链接：https://blog.csdn.net/qq_44663150/article/details/119681400

版权

postgresql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

– 当天
select distinct to_char(created_time,‘yyyy-mm-dd’),count(status) from diku_mod_combine_catalogue.task where to_char(created_time,‘dd’)=to_char(CURRENT_DATE,‘dd’)
group by to_char(created_time,‘yyyy-mm-dd’)
order by to_char(created_time,‘yyyy-mm-dd’)

– 昨天
select distinct to_char(created_time,‘yyyy-mm-dd’),count(status) from diku_mod_combine_catalogue.task where to_char(created_time,‘dd’)=to_char((CURRENT_DATE-“interval” ‘1 day’),‘dd’)
group by to_char(created_time,‘yyyy-mm-dd’)

– 本周按天分组
select distinct to_char(created_time,‘yyyy-mm-dd’),count(status) from diku_mod_combine_catalogue.task where to_char(created_time,‘yyyy-mm-dd’)>=to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,‘D’),‘99’)-2) ||’ days’ as interval),‘yyyy-mm-dd’) and to_char(created_time,‘yyyy-mm-dd’)<=to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,‘D’),‘99’)-2)+6 ||’ days’ as interval),‘yyyy-mm-dd’) and status=‘PENDING_CATALOG’
group by to_char(created_time,‘yyyy-mm-dd’)

– 本月按天分组
select distinct to_char(created_time,‘yyyy-mm-dd’),count(status) from diku_mod_combine_catalogue.task where to_char(created_time,‘yyyy-mm’)=to_char((select now() as timestamp),‘yyyy-mm’)
group by to_char(created_time,‘yyyy-mm-dd’)

–今年按月分组
select distinct to_char(created_time,‘yyyy-mm’),count(status) from diku_mod_combine_catalogue.task where to_char(created_time,‘yyyy’)=to_char((select now() as timestamp),‘yyyy’)
group by to_char(created_time,‘yyyy-mm’)

–去年按月分组
select distinct to_char(created_time,‘yyyy-mm’),count(status) from diku_mod_combine_catalogue.task where to_char(created_time,‘yyyy’)=to_char((select now()-“interval” ‘1 years’),‘yyyy’)
group by to_char(created_time,‘yyyy-mm’)