0
I wanted to add up all the values every 2 minutes and group those same values
valor tempo
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 wanted was this:
valor tempo
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, missing add and make the group by time, however I am not able to do it
SET search_path TO estufa;
SELECT (medi_sensor.valor/3600) *220 , tempo
FROM medi_sensor
JOIN sensor ON medi_sensor.sensor_nome = sensor.nome
JOIN mote ON num_mot=mot_id
JOIN divisao ON divisao_id=id_div
WHERE medi_sensor.sensor_nome LIKE 'corrente%' AND divisao.nome='MORANGOS' AND tempo>'2019-05-22' AND tempo<'2019-05-24'
ORDER BY tempo ASC
This "every two minutes" and : two minutes starting from each initial time or counting intervals of two minutes 00:00:00 00:02:00:04:00 00:06:00 ?
– Motta
I don’t know if I understand this correctly, but you can use the Set Returning Function: generate_series (shorter time of your table, longer time of your table, '00:02:00':interval) , and use the result in a junction with your table with the corresponding GROUP BY.
– anonimo
@Motta 2 minutes from the initial time, if starting time = 17:00 then the table is 17:00, 17:02, 17:04, 17:06.... etc.
– Fábio Morais
@anonimo Yes, I know there is such a function, but I haven’t had a month since learning sql and so I can’t do that kind of logic yet.
– Fábio Morais
Try to combine this https://dba.stackexchange.com/questions/158907/how-do-i-generate-a-time-series-in-postgresql with your sql a series for each minimum and maximum intervalk "value" a sql would do a between to mount the https://dba.stackexchange.com/questions/160654/postgres-sum-time-of-this-interval-and-group-by-30-and-30-minutes
– Motta