Calculate the difference between data timestamp and recorded timestamp

  • What Grafana version and what operating system are you using?
    Grafana 9.0.4 / Docker’d

  • What are you trying to achieve?
    Every 15s, my IOT device pushes, what it believes, is the current timestamp. I’d like to calculate the difference between the timestamp received/recorded by InfluxDB and the value of the timestamp given by IOT device. In theory, if everything is sync’d, this should be 0.

Basically, I’m trying to use this to determine if the IOT device goes offline, meaning there was a 15s datapoint missed (or multiple). Is there a better way to achieve this vs what I’m trying to do?

  • How are you trying to achieve it?
    This is what I currently have. I’m not sure if it is correct for what I’m trying to do.
from(bucket: "home_metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "GarageDoor_Ping")
  |> filter(fn: (r) => r["_field"] == "value")
  |> derivative(nonNegative: true)
  |> map(fn: (r) => ({r with _value: r["_value"] - 1000.0}))
  • What happened?
    It’s working, but I don’t know if this is the correct way.

Hi @jackt

So in your InfluxDB tables, you have two timestamps? One received/recorded by InfluxDB and another that comes through as, I guess, another timestamp and gets saved in the tables? I don’t think I have ever seen something like this, but hey ho, nobody says you can’t do that.

If you want to determine if the device goes offline, I would recommend you use the deadman check alert offered by InfluxDB OSS. It’s easy to set up and works great (this is what I use in conjunction with Slack).

No. Sorry if I was not clear. There is 1 timestamp that my IOT pushes as data (aka: the value). Each time Influx receives a data point, Influx records that data along with the timestamp of when that value was recorded (the record’s metadata).

If it makes it easier to understand, change the data my IOT pushes to be to the current temperature (or generically, the value ‘X’). It’s just 1 explicit datapoint that is saved by InfluxDB, along with the timestamp of when that data was received.

I’ll take a look at that check. thanks.