I am attempting to do some time-series data querying from InfluxDB.
Let’s say I had the following data:
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:
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:
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:
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.