Calcualte column/value from group query or using timespans with gapped data?

1

I want to calculate the difference is 2 columns. I’m having difficulty.

Running a group query where I get 2 temperatures based on time and calculate the difference.

SELECT mean(tempa) mean(tempb) FROM test where time >= ‘2017-02-10T21:00:00Z’ AND time <= ‘2017-02-17T20:00:00Z’ GROUP BY time(5s) fill(previous)

time, tempa, tempb
2017-02-10T21:00:00Z 68.94978800000001 49.603880000000004
2017-02-10T22:00:00Z 71.21408120000001 48.41809333333335
2017-02-10T23:00:00Z 76.04772800000002 45.94975533333332
2017-02-11T00:00:00Z 77.6948528 44.904361333333334

looking to return:
time, tempa, tempb
2017-02-10T21:00:00Z 68.94978800000001 49.603880000000004 19.xxxxx

If I add (tempa-tempb) AS C to the query I get
ERR: error parsing query: mixing aggregate and non-aggregate queries is not supported

If I try to run this I get holes in the data because the sensors are not recording the in exact same time.
SELECT tempa , tempb FROM test where time >= ‘2017-02-10T21:00:00Z’ AND time <= ‘2017-02-17T20:00:00Z’ fill(previous)

time, temp a, temp b
2017-02-17T19:06:44Z 45.106840000000005
2017-02-17T19:06:47Z 71.51712800000001
2017-02-17T19:07:44Z 45.40888000000001
2017-02-17T19:07:48Z 72.00039200000003
2017-02-17T19:08:44Z 45.07328000000001
2017-02-17T19:08:47Z 71.39631200000002
2017-02-17T19:09:44Z 44.939040000000006
2017-02-17T19:09:49Z 71.81916800000002
2017-02-17T19:10:44Z 44.73768
2017-02-17T19:10:48Z 72.06080000000003

fill previous does not work on this kind of query.

How do you do a calculation tempa - tempb = tempc when the data has a time span gap like this?

2

So I think I figured out how to do it. Use a nested query, do the calc in the inner then group by outer. Hopefully this helps someone or find a prob with it if you can…

SELECT mean(tempa) as tempa, mean(tempb) as tempb, mean(tempc) as tempc
FROM (SELECT tempa, tempb, (tempa - tempb) AS tempc FROM measurement fill(previous))
WHERE time >= ‘2017-02-10T21:00:00Z’ AND time <= ‘2017-02-17T20:00:00Z’ GROUP BY time(24h) fill(previous)