'How to fetch data in a given interval in postgresql

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

postgresqlpostgispsqlpostgresql-9.1


Solution 1:[1]

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"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mitesh Kothari

Related Questions