0

I have a table iot that has some sensor data, one of the columns is booleans as indicators that some event occurred, how can I calculate how long ago the most recent true value happened in a column using SQL?

The example data set looks something like the following:

ts sensor
2021-04-07T17:12:36.314688Z true
2021-04-07T17:12:43.995006Z false
2021-04-07T17:12:47.447316Z false
2021-04-07T17:12:53.276256Z true
2021-04-07T17:12:58.723072Z false
djbobo
  • 487
  • 2
  • 6

1 Answers1

1

If you want to do this in SQL, it's possible to use datediff() for calculating the difference in time between your timestamp and now.

This query returns two columns:

  • the timestamp of the record
  • the duration in seconds from the timestamp until now
select datediff('s', now(), iot.ts) diff, ts 
from iot

Example response:

diff ts
56241 2021-04-07T17:12:36.314688Z
56233 2021-04-07T17:12:43.995006Z
56230 2021-04-07T17:12:47.447316Z
56224 2021-04-07T17:12:53.276256Z
56219 2021-04-07T17:12:58.723072Z

If you want to have the latest reading for a value, you can use:

select datediff('s', now(), iot.ts) diff, ts 
from iot
latest by sensor where sensor

This will perform where filtering first to return rows where sensor=true then on the remaining rows, return the latest record:

diff ts
56224 2021-04-07T17:12:53.276256Z

For the units of datediff, you can use seconds, minutes, hours, days, months or years. For more info, see the documentation for datediff

Brian Smith
  • 1,222
  • 7
  • 17