如何在PostgreSQL中以给定的间隔获取数据

发布于 2025-01-23 23:01:45 字数 508 浏览 5 评论 0原文

SELECT time,CEIL(AVG(value)) from table 
where col1 = 1 
   and col2='matchThis' 
   and col3>='2022-04-10T18:30:00.00Z' 
   and col3<='2022-04-25T12:58:23.00Z' 
   and mod(to_char(col3, 'MI')::int, 15)=0 
GROUP BY time

查询以获取15分钟间隔数据的SEMPLE响应

25-04-2022 01:00
25-04-2022 01:15
25-04-2022 01:30
25-04-2022 01:45

上述查询在15、30和60分钟间隔内正常工作，但我必须如下所示的选项创建一个查询返回间隔数据。

15分钟

30分钟

1小时

2小时

6小时

12小时

1天

原文

SELECT time,CEIL(AVG(value)) from table 
where col1 = 1 
   and col2='matchThis' 
   and col3>='2022-04-10T18:30:00.00Z' 
   and col3<='2022-04-25T12:58:23.00Z' 
   and mod(to_char(col3, 'MI')::int, 15)=0 
GROUP BY time

Semple response of the query to get 15-minute interval data

25-04-2022 01:00
25-04-2022 01:15
25-04-2022 01:30
25-04-2022 01:45

The above query works fine in 15, 30, and 60 minutes intervals but I have to create a query return interval data as the option shown below.

15 minutes

30 minutes

1 hour

2 hours

6 hours

12 hours

1 day

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不离久伴 2025-01-30 23:01:45

SELECT 
   ceil(avg(column_name)), 
   to_timestamp(floor((extract('epoch' from column_name) / 600 )) *600) 
   AT TIME ZONE 'UTC' as interval 
FROM table_name  
   WHERE id=1 
      and column='value' 
      and col >='2022-04-21' 
      and col <= '2022-04-30' 
      GROUP BY interval ORDER BY interval ASC"

SELECT 
   ceil(avg(column_name)), 
   to_timestamp(floor((extract('epoch' from column_name) / 600 )) *600) 
   AT TIME ZONE 'UTC' as interval 
FROM table_name  
   WHERE id=1 
      and column='value' 
      and col >='2022-04-21' 
      and col <= '2022-04-30' 
      GROUP BY interval ORDER BY interval ASC"

回复收藏 0 原文

~没有更多了~