Need help with continuous query to save different between two timestamps

I want to find the difference between the values from two subsequent timestamps using continuous queries and store them into a seperate measurement

What i want to do is to compare the two timestamps and check if they belong to the same day and if they do perform the difference operation otherwise, I would insert a 0 into the measurement

 time                            v1       v2      v3            gatewayId

02/05/18 23:59:39 76442 89105 79666 123
02/05/18 23:59:51 76475 89142 79700 123
03/05/18 0:00:03 32 36 34 123
03/05/18 0:00:15 65 73 68 123

  time                           p1   p2  p3      gatewayId

02/05/18 23:59:39 0 0 0 123
02/05/18 23:59:51 33 37 34 123
03/05/18 0:00:03 0 0 0 123
03/05/18 0:00:15 33 37 34 123

The best query i could come up with was this
CREATE CONTINUOUS QUERY ‘ph’ on ‘enigma’
RESAMPLE EVERY 1s
BEGIN
subtract the difference between the two ( SELECT * FROM electricity where gatewayId=“12323” ORDER BY DESC LIMIT 2 tz(‘Europe/Dublin’) )
END

1 Like