CODEWITHSUNDEEP
sqlpostgresql
Fábio Morais
Fábio Morais

Reputation: 272

Sum all values in a column and group by 2 minutes

I want to sum all values in a column "value" and group by a interval of 2 minutes

I have values like this:

value     TIME
0.3    2019-05-22 01:11:45---> first value 0,3
0.3    2019-05-22 01:12:16-----|
0.3    2019-05-22 01:13:26-----|second value 0,6
0.2    2019-05-22 01:13:56---|
0.4    2019-05-22 01:14:06---|
0.6    2019-05-22 01:15:43 --|third value 1,2

But what I really want is like this:

value     TIME
0.3    2019-05-22 01:11:45
0.6    2019-05-22 01:13:45
1.2    2019-05-22 01:15:45

My code in postgresql:

SELECT medi_sensor.value, time
FROM medi_sensor
    JOIN sensor ON medi_sensor.sensor_name = sensor.name
    JOIN mote ON num_mot=mot_id 
    JOIN room ON room_id=id_div
WHERE medi_sensor.sensor_name LIKE 'current%' AND room.name='DIV' AND time>'2019-05-22' AND time<'2019-05-24'
ORDER BY time ASC

The problem is how to group by minute to minute in my time column

Upvotes: 0

Views: 187

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In Postgres, you can use generate_series() to generate the values:

select gs.t, sum(value)
from (select ms.value, time, min(time) over () as min_time, max(time) over () as max_time
      from medi_sensor ms join
            sensor s
            on ms.sensor_name = s.name join
            mote 
            on num_mot = mot_id join
            room r
            on room_id = id_div
      where ms.sensor_name LIKE 'current%' and
            r.name = 'DIV' and
            time > '2019-05-22' and
            time < '2019-05-24'
     ) x right join lateral
     generate_series(min_time, max_time, interval '2 minute') gs(t)
     on time >= gs.t and time < ts.t + interval '2 minute'
order by gs.t;

I would recommend that you use table aliases for all column references in your query.

EDIT:

with x as (
      select ms.value, time
      from medi_sensor ms join
           sensor s
           on ms.sensor_name = s.name join
           mote 
           on num_mot = mot_id join
           room r
           on room_id = id_div
      where ms.sensor_name LIKE 'current%' and
            r.name = 'DIV' and
            time > '2019-05-22' and
            time < '2019-05-24'
     ) x 
select gs.ts, sum(x.value)
from (select generate_series(min_time, max_time, interval '2 minute') as ts
      from (select min(time) as min_time, max(time) as max_time
            from x
           )
      ) gs left join
      x
      on x.time >= gs.t and x.time < ts.t + interval '2 minute'
group by gs.ts
order by gs.ts;

Upvotes: 1

Related Questions