Query for Time Series to include previous value if not within applied time range?


I spent many hours trying to find a solution to this.

So at 2 pm, I turn ON the light switch, and at 10 pm I turn OFF the light switch.

So if I apply the time range between 4pm and 7pm it will show NO DATA, instead of giving me the answer, YES the light was ON between 2pm and 10pm.

That would be the expected output from the user’s point of view.

This is my query.

SELECT mean(“value”) FROM “mcsMQTT” WHERE (“device” = ‘Test Button’) AND $timeFilter GROUP BY time($__interval) fill(previous)

To my understanding, if there is no value in the applied time range, the query should ask for the previous one to be able to show the correct state.

How to do that?

Thank you

Hi @kdiamond

Have you tried changing the time field in your query to be time <= now(), like this:


SELECT sum("value") FROM ....  WHERE .... AND time <= now() GROUP BY region, time(1h)