Can I get all data since a value changed in one query?

1

Hi there!

I’m using influxdb since forever at home and I’m running into an issue for the first time now.
My phone is sending it’s location via tasker to my influxdb based on my location and activity.
The format looks like this:

time                acc   activity altitude bat bearing device latitude   longitude  speed
----                ---   -------- -------- --- ------- ------ --------   ---------  -----
1724406917064864677 5.28  1        125.5    61  353.8   bv9300 61.xxxxxxx 24.xxxxxxx 0.13
1724406923162652605 2.4   1        123.9    61  136.7   bv9300 61.xxxxxxx 24.xxxxxxx 0
1724406928419869161 2.4   1        112.06   61          bv9300 61.xxxxxxx 24.xxxxxxx 0
1724406934076331795 2.2   1        111.94   61          bv9300 61.xxxxxxx 24.xxxxxxx 0
1724406939315877567 2.2   1        111.94   61          bv9300 61.xxxxxxx 24.xxxxxxx 0
1724406944934750806 2.6   1        112.05   61          bv9300 61.xxxxxxx 24.xxxxxxx 0.02
1724406950150411841 2.5   1        123.9    61  342.1   bv9300 61.xxxxxxx 24.xxxxxxx 0
1724406955755602850 2.5   1        112.06   61          bv9300 61.xxxxxxx 24.xxxxxxx 0.01
1724406965616635671 2.98  1        112.14   61  27.4    bv9300 61.xxxxxxx 24.xxxxxxx 0.11
1724406972585129537 3.58  0        112.05   61  182.5   bv9300 61.xxxxxxx 24.xxxxxxx 0.16
1724406973121168124 3.32  0        112.05   61  174.2   bv9300 61.xxxxxxx 24.xxxxxxx 0.16

I know I can select all rows where activity = 1. What I would like to do is to select all rows where activity = 1 since the moment it changed to 1. Is that possible in one query?

2

Which version of InfluxDB are you using, here is a possible solution with 2 queries:

  1. Find the last time activity changed to 1 and get the the timestamp of the last time activity was set to 1.
SELECT last(time) 
FROM your_measurement 
WHERE activity = 1
  1. Fetch all rows from that time onward. Now that you have the timestamp from the first query, you can plug it into the second query to fetch all rows with activity = 1 from that time onwards.
SELECT *
FROM your_measurement
WHERE time >= <timestamp_from_first_query> AND activity = 1
3

Thanks! I’m using 1.8, so both influxql and flux work. I was hoping there’s a single query option but I use 2 steps then. Thanks again!

4

Hm, select last(time) from location where activity = 1 doesn’t give me any result…
select * from location where activity = 1 and time >= now() -24h outputs as expected, though. I guess, I have to do some reading.

1 Like