'Query to find out the exact last hour data in Postgresql

I want the average data from the last hour in short if the current time is 2:30, the query should return the data from 1:00 to 2:00 and the average of 1:00 to 2:00 (which will be only one value (not changing).

time            data       Avg_data (There should be one output)
1/2/2022 1:01   1.2          1.5416
1/2/2022 1:02   1.3
1/2/2022 1:03   1.4
1/2/2022 1:04   1.1
1/2/2022 1:05   1.2
1/2/2022 1:07   1.2
1/2/2022 1:08   1.5
1/2/2022 1:09   1.7
1/2/2022 1:10   1.6
1/2/2022 1:11   1.5
1/2/2022 1:12   1.4
1/2/2022 1:13   1.8
1/2/2022 1:14   1.8
1/2/2022 1:15   1.8
1/2/2022 1:16   2.1
1/2/2022 1:17   2.9
1/2/2022 1:21   1.8
1/2/2022 1:22   1.9
1/2/2022 1:23   1.8
1/2/2022 1:24   1.5
1/2/2022 1:25   1.6
1/2/2022 1:26   1.6
1/2/2022 1:27   1.5
1/2/2022 1:28   1.5
1/2/2022 1:29   1.5
1/2/2022 1:30   1.3
1/2/2022 1:31   1.3
1/2/2022 1:32   1.4
1/2/2022 1:33   1.7
1/2/2022 1:34   1.8
1/2/2022 1:35   1.7
1/2/2022 1:36   1.5
1/2/2022 1:37   1.6
1/2/2022 1:38   1.3
1/2/2022 1:39   1.4
1/2/2022 1:43   1.7
1/2/2022 1:44   1.3
1/2/2022 1:45   1.4
1/2/2022 1:46   1.4
1/2/2022 1:47   1.1
1/2/2022 1:48   1.3
1/2/2022 1:49   1.2
1/2/2022 1:50   1.4
1/2/2022 1:51   1.3
1/2/2022 1:52   1.5
1/2/2022 1:53   1.5
1/2/2022 1:57   1.4
1/2/2022 1:58   1.2
1/2/2022 1:59   1.4
1/2/2022 2:00   1.3

My query looks something like

SELECT time, avg(data)
FROM exercise 
WHERE  
time >= (NOW() - INTERVAL '1 hour')
group by time,data;
sqlpostgresql


Solution 1:[1]

This is the solution:

select avg("data") from yourtable
where "time">=(date_trunc('hour',NOW()::timestamp) - INTERVAL '1 hour')
and
"time"<=date_trunc('hour',NOW()::timestamp);

sqlfiddle was showing null as data gets old with time.. please check at your end.

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 nikhil sugandh

Related Questions