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

[kdiamond]

Hi.

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
Br,
kd

[Pooh]

I would say that you are not measuring the value of the data you want to know
abut.

If you want to know “how long was the light on for?” then you should record
when the light is on (and when it is off). Depending on how accurately you
want the answer to “how long was the light on for?” to be (hours, minutes,
seconds?) then you should record whether the light is on with at least twice
that resolution (in other words, if you want an answer accurate to the nearest
hour, you record whether or not the light is on every 30 minutes; if you want
an answer accurate to the nearest minute, you record every 30 seconds, etc).

The important point is that because you want to know how long the light was on
for, you record “whether the light is on”.

If instead you record “when did the light get turned on or off?” then you can
only answer “did the light get turned on (or off) during this time period?” and
that is not the question you are ultimately trying to answer.

You have to record the data you need in order to answer the question you want.

Antony.

[kdiamond]

Thank you for your reply.

My request is actually a default behavior for mysql.

SELECT lastdate AS TIME,value
FROM mcsmqtt
WHERE Device = ‘Test_Test_Test Button’
ORDER BY 1 DESC

It will show the entire graph if Time picker is selected outside the measurements made. This also works using InfluxDB.

or inside

…where doing this with InfluxDB will say NO DATA.

This might also be something between Grafana an InfluxDB

Br,
kd