I am attempting to do some time-series data querying from InfluxDB.
Let’s say I had the following data:
2019-12-10T00:00:00 19121000
2019-12-10T01:00:00 19121001
2019-12-10T02:00:00 19121002
2019-12-10T03:00:00 19121003
2019-12-10T04:00:00 19121004
2019-12-10T05:00:00 19121005
2019-12-10T06:00:00 19121006
2019-12-10T07:00:00 19121007
2019-12-10T08:00:00 19121008
2019-12-10T09:00:00 19121009
2019-12-10T10:00:00 19121010
2019-12-10T11:00:00 19121011
2019-12-10T12:00:00 19121012
2019-12-10T13:00:00 19121013
For my purposes, the value of the data in between timestamps is assumed to be the previous value stored in InfluxDB. So, for example, the value at 2019-12-10T01:23:45 would be 19121001.
Let’s say I now need to get the data between 2019-12-10T00:37:00 and 2019-12-10T12:30:00 (inclusive), in 3-hour intervals.
This means the results I should get are:
2019-12-10T00:37:00 19121000
2019-12-10T03:37:00 19121003
2019-12-10T06:37:00 19121006
2019-12-10T09:37:00 19121009
I need help with crafting a single query to do this. I think changing the timestamps to be in-between the actual data is not really important, because my code that will be submitting this query can always handle that part.
However, I am having trouble in crafting a single query that will achieve this. I first tried
SELECT FIRST(value) FROM test WHERE time >= ‘2019-12-10 00:37:00’ AND time <= ‘2019-12-10 12:30:00’ GROUP BY time(3h) FILL(previous)
However, this does not return the correct result, because it lacks access to the “previous” value before 2019-12-10 00:37:00:
2019-12-10T00:00:00 19121001
2019-12-10T03:00:00 19121003
2019-12-10T06:00:00 19121006
2019-12-10T09:00:00 19121009
2019-12-10T12:00:00 19121012
So I tried to include the “previous” value to the lower time bound in the query results:
SELECT FIRST(value) FROM (SELECT value FROM (SELECT value FROM test WHERE time < ‘2019-12-10 00:37:00’ LIMIT 1), (SELECT value FROM test WHERE time >= ‘2019-12-10 00:37:00’ AND time <= ‘2019-12-10 12:30:00’)) WHERE time <= ‘2019-12-10 12:30:00’ GROUP BY time(3h) FILL(previous)
Unfortunately, this has problems as well, because it gives results below the lower time bound:
2019-12-09T00:00 19120900
2019-12-09T03:00 19120900
2019-12-09T06:00 19120900
2019-12-09T09:00 19120900
2019-12-09T12:00 19120900
2019-12-09T15:00 19120900
2019-12-09T18:00 19120900
2019-12-09T21:00 19120900
2019-12-10T00:00 19121001
2019-12-10T03:00 19121003
2019-12-10T06:00 19121006
2019-12-10T09:00 19121009
2019-12-10T12:00 19121012
Why is this going below the lowest time bound that would be returned by the subquery?
Would I be able to get some advice on how I could use a single query to get what I need? I also tried to look for information in the documentation on how to pull times and use them in subqueries, but did not have any success.
