Hello, i have a scenario where we want to get values for a particular time range and also values beyond that range.
So for example we want values from time T1 to time T2 .
T1-----------------T2 . I know we can get it by where time is <= T2 and >= T1 . what i also want is values beyond that range ( i can’t change the time range) so like this
value1------T1---------------T2---------value2.
Surely that simply means your query needs to contain “time >= value1 and time
<= value2”?
Or am I missing something about your requirement?
Antony.
thing is i don’t know if there will be value there or not . Let’s say i want values from today’s 12 pm to 3pm and there is no value at 12pm so it should take one previous value before it . it can be at 11:30 or 11:55 anything but last previous value. same for 3pm . if there is no value at 3pm then go further like 3:30 or anything wherever there is value.
I hope i was able to make it clear. If not i have a diagram below.
so if you see graph i want the values in that time range and also one before and after it. I can’t change time range.
Oh, so “T1” and “T2” are timestamps, but “value1” and “value2” are not…
I can’t think of a way to combine these into a query; maybe someone else can.
Regards,
Antony.
yes T1 and T2 are timestamps yes. value is any data point or you can say “fields”
I am not sure if we can do it in a single query.
i have tried this query but it doesn’t seem to give results that i want. It’s giving result in range of T1 and T2 only
select * FROM (select * from Table_name where time <= '2019-07-04T09:43:00Z' group by "name" order by desc limit 1),(select * from Table_name where time >= '2019-07-04T09:50:00Z' group by "name" order by desc limit 1), Table_name WHERE time >= '2019-07-04T09:43:00Z' AND time <= '2019-07-04T09:50:00Z' group by "name" order by descI think you’ll have to do it as three separate queries.
First, get the measurements between T1 and T2:
SELECT value FROM measurement WHERE time>=T1 AND time<=T2
Second, if the first measurement’s timestamp is after T1, do this:
SELECT value FROM measurement WHERE time<T1 ORDER BY time DESC LIMIT 1
Finally, if the last measurement (from the first query) is before T2, do this:
SELECT value FROM measurement WHERE time>T2 LIMIT 1
Hope this helps.
Jeremy Begg
Thanks for the help JeremySTX. I figured that out as separate queries but is there any way to perform all these in 1 single query IF NOT then how can i store the result of previous query and pass it to next query ??
Note that these are not just separate queries, but the “outer” ones can be quite expensive since they have “open” time ranges. Imagine there is not a single value left of the interval, and InfluxDB would go across all shards to find that value (e.g. like a “full table scan” in SQL). InfluxDB should be able to “search backwards” and stop once it finds the first (LIMIT) values…
As for the case right of the interval, this might well be, since it’s the default ordering.
But left of the interval can be expensive!
Any thoughts on this? (besides “closing” the range and assuming, there should be a value within a certain time range).
Did a bit of research and it’s actually discussed a lot:
