Get data from series where the time range is defined in another series

1

Hello,

i have following query working:

select sum(value) from Watermeter_pulse where time >1614169667377061953 and time < 1614169734060662484

Now I want to have the time values comming from a different series:
First value:select last(value),time from Waterpump_status_bin where value=1
Last value: select last(value),time from Waterpump_status_bin where value=0

I saw it is not possible to just do the following:

select sum(value) from Watermeter_pulse where time >(select last(value),time from Waterpump_status_bin where value=1) and time < (select last(value),time from Waterpump_status_bin where value=0)

Do you have any clue how to archive this?
Thank you

2

On a quick look through the docs, it looks like what you’re trying to do should be possible.

Are you getting a specific error or something? Can you paste the full query and a couple of rows of the raw data from both tables that show the issue?

3

Hello,

Thank you for the reply.
I am using InfluxDB shell version: 1.6.4.

Here are the results of the queries:

select last(value),time from Waterpump_status_bin where value=1
name: Waterpump_status_bin
time                last
----                ----
1614280903984986360 1

select last(value),time from Waterpump_status_bin where value=0
name: Waterpump_status_bin
time                last
----                ----
1614280985565985634 0

select sum(value) from Watermeter_pulse where time >1614280903984986360 and time < 1614280985565985634
name: Watermeter_pulse
time                sum
----                ---
1614280903984986361 10


select sum(value) from Watermeter_pulse where time >(select last(value),time from Waterpump_status_bin where value=1) and time < (select last(value),time from Waterpump_status_bin where value=0)
ERR: error parsing query: found SELECT, expected identifier, string, number, bool at line 1, char 54

So as you can see the query is not even parsed successfully.

4

Just a guess, but dont think selecting multiple columns is supported in sub queries - at least in the way you’re doing.

Give this a try:

select sum(value) from Watermeter_pulse 
where time >(select last(time) from Waterpump_status_bin where value=1) 
and time < (select last(time) from Waterpump_status_bin where value=0)
5

Hello,

If I try this I get the same error:

select sum(value) from Watermeter_pulse where time >(select last(time) from Waterpump_status_bin where value=1) and time < (select last(time) from Waterpump_status_bin where value=0)
ERR: error parsing query: found SELECT, expected identifier, string, number, bool at line 1, char 54