If data is received periodically because it is provided on a change of valve or just because measuring times are sporadic, how is data be queried with that falls between measurements?
Or through example, how can I determine the average flow over the range from 2017-07-29 00:00:00 to 2017-08-03 00:00:00?
Time Flow
2017-07-25 15:38:04 40.43
2017-07-28 21:09:51 43.50
2017-08-04 02:52:31 47.14
2017-08-08 00:40:36 47.66
2017-08-11 06:50:59 47.27
2017-08-14 17:43:02 50.18
2017-08-17 16:00:07 48.76
2017-08-21 10:42:56 44.20
As shown, it is not possible to do so. True? I expect query durations must always be above a given span which the application that writes data ensure that records are inserted within that duration. True? Thank you
> SELECT value FROM "point_74" WHERE time >= '2017-07-30T02:35:00Z' AND time <= '2017-07-30T02:45:00Z'
name: point_74
time value
---- -----
2017-07-30T02:35:49Z 696.73
2017-07-30T02:36:49Z 696.748
2017-07-30T02:37:50Z 696.768
2017-07-30T02:38:50Z 696.785
2017-07-30T02:39:50Z 696.803
2017-07-30T02:40:50Z 696.822
2017-07-30T02:41:51Z 696.84
2017-07-30T02:42:51Z 696.857
2017-07-30T02:43:51Z 696.877
2017-07-30T02:44:51Z 696.895
> SELECT MEAN("value") FROM "point_74" WHERE time >= '2017-07-30T02:42:51.1Z' AND time <= '2017-07-30T02:42:51.9Z'
> SELECT MEAN("value") FROM "point_74" WHERE time >= '2017-07-30T02:42:50.9Z' AND time <= '2017-07-30T02:42:51.1Z'
name: point_74
time mean
---- ----
2017-07-30T02:42:50.9Z 696.857
>
Or based on example data from the following, how can I query the water level in santa_monica at 2015-09-18T21:40:00Z?
EDIT. Guess this can be performed with LAST().
> SELECT * FROM "h2o_feet"
name: h2o_feet
--------------
time level description location water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938