Get data from multiple selects in PostgreSql (using DBeaver)

Question

I have a table like this:

10/10/2018 00:00 | 5
10/10/2018 00:10 | 7
10/10/2018 00:20 | 9
...
10/10/2018 23:40 | 5
10/10/2018 23:50 | 6

I need to get average number in hour, and return all hour averages (from 00:00 to 23:00)

I can get two hour averages like this:

(select avg(value)
from public.table
where time_stamp between TO_TIMESTAMP('2018-11-05 20:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and
TO_TIMESTAMP('2018-11-05 20:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 hour')
UNION ALL
(select avg(value)
from public.table
where time_stamp between TO_TIMESTAMP('2018-11-05 21:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and
TO_TIMESTAMP('2018-11-05 21:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 hour')

But how can I got all 24 average values in some loop? I dont want to copy this SQL 24 times.

You might try truncating the timestamp down to the hour, then grouping your results by that truncated timestamp? — Nicholas Hirras
– Nicholas Hirras, Commented Nov 5, 2018 at 21:23
Not really understand how it need to work (my english skills are bad), can you give simple example? — Mark Afanasevich
– Mark Afanasevich, Commented Nov 5, 2018 at 21:24
Something like this: select date_trunc('hour', time_stamp), avg(value) from public.table where date_trunc('day', time_stamp) = date_trunc('day', TO_TIMESTAMP('2018-11-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) group by date_trunc('hour', time_stamp); — Nicholas Hirras
– Nicholas Hirras, Commented Nov 5, 2018 at 21:26
I don't have a postgres machine available to test this on right now, but do a very similar thing in Oracle. — Nicholas Hirras
– Nicholas Hirras, Commented Nov 5, 2018 at 21:27
This script returns values of only first hour of day, but I need for all hours of day (total 24) — Mark Afanasevich
– Mark Afanasevich, Commented Nov 5, 2018 at 21:38

Nicholas Hirras · Accepted Answer · 2018-11-05 21:58:37Z

2

Give this a try

select 
   date_trunc('hour', time_stamp), avg(value) 
from 
   public.table 
where 
   date_trunc('day', time_stamp) = date_trunc('day', TO_TIMESTAMP('2018-11-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) 
group by 
   date_trunc('hour', time_stamp);

The idea is to make all the timestamps only specific down to the hour. Then select all the records that appear on that day, grouped by hour. Perform your avg over the values within each hour grouping.

answered Nov 5, 2018 at 21:58

Nicholas Hirras

2,5962 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get data from multiple selects in PostgreSql (using DBeaver)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related