How to Get all data entry points in a specific time range and also beyond it

InfluxDB 2
1

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.

2

Surely that simply means your query needs to contain “time >= value1 and time
<= value2”?

Or am I missing something about your requirement?

Antony.

3

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.

graph

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.

4

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.

5

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.

6

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 desc
7

I 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

1 Like
8

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 ??