query in postgres by timestamp minutes

Question

I'm wondering how to do a group by query where I need to group all the data of the same minute in my db to sum all of them.

ws_controller_hist=>  SELECT timestamp, type, medium from default_dataset where type like 'status_devices' and timestamp> current_timestamp - interval '60 minutes' and organization_id = '9fc02db4-c3df-4890-93ac-8dd575ca5638' order by timestamp asc;

       timestamp        |      type      | medium
------------------------+----------------+--------
 2017-12-17 12:44:00+00 | status_devices | {1,0}
 2017-12-17 12:44:00+00 | status_devices | {1,0}
 2017-12-17 12:44:00+00 | status_devices | {1,0}
 2017-12-17 12:44:01+00 | status_devices | {1,0}
 2017-12-17 12:44:01+00 | status_devices | {1,0}
 2017-12-17 12:44:10+00 | status_devices | {0,1}
 2017-12-17 12:44:10+00 | status_devices | {0,1}

Doing this query

ws_controller_hist=>  SELECT timestamp, type, sum(medium[1]) from default_dataset where type like 'status_devices' and timestamp> current_timestamp - interval '60 minutes' and organization_id = '9fc02db4-c3df-4890-93ac-8dd575ca5638' group by timestamp, type order by timestamp asc;

       timestamp        |      type      | sum
------------------------+----------------+-----
 2017-12-17 12:44:00+00 | status_devices |   3
 2017-12-17 12:44:01+00 | status_devices |   2
 2017-12-17 12:44:10+00 | status_devices |   0

I want to get 5 in sum, which is the total in the minute 44 understand?

Gordon Linoff · Accepted Answer · 2017-12-17 13:11:38Z

1

I think you want date_trunc():

select date_trunc('minute', timestamp) as timestamp_min, type, 
       sum(medium[1])
from default_dataset
where type like 'status_devices' and
      timestamp > current_timestamp - interval '60 minutes' and
      organization_id = '9fc02db4-c3df-4890-93ac-8dd575ca5638'
group by timestamp_min, type
order by timestamp_min asc;

answered Dec 17, 2017 at 13:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

query in postgres by timestamp minutes

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