How to update a SQLite column with a value that is a function of the current time-CodePudding

I have an IoT project that subscribes to sensor data via MQTT, saves the data to a local SQLite3 DB, does some processing on the data, then publishes a handful of new messages via MQTT every 60 seconds. I have one SQLite table whose schema is something like this (I've omitted irrelevant columns):

sensor

id integer PRIMARY KEY,
last_heard text (ISO String),
first_heard text (ISO String),
certainty real CHECK(certainty >= 0 and certainty <= 1),
is_monitored boolean

Each row in the table represents a unique sensor. certainty gets initialized at .5 when a sensor is first heard. For all sensors where is_monitored is True, every two minutes that the sensor is NOT heard, certainty needs to be decremented by .05.

In other words, for monitored sensors,

certainty = certainty - (.05 * ((time_now_minutes - last_heard_minutes) // 2))

How is this best accomplished in Python and/or SQLite3? Any help is greatly appreciated.

Here's what I've tried:

I tried creating a View in the Database in order to have a computed column that held the amount of time unseen for each sensor/row which allowed me to compute the so-called "adjusted_certainty" as another column in the View. The problem with this approach was that you can't put constraints on computed columns in Views in SQLite, so the adjusted_certainty was going below 0, and it forced me to keep track of two versions of certainty from the Python code - the first being the writable certainty in the sensor table, and the second being the read-only adjusted_certainty in the View.
In my Python script, I'm able to get all monitored sensors, calculate how long each of them hasn't been seen, and update their certainties accordingly. The problem is, I don't know when to do this. If I do it before my outgoing messages to MQTT are sent, it seems like weird race conditions occur and data gets out of order in either the database, my outgoing messages, or both. If I update the certainties afterwards, then they wouldn't be accurate in the outgoing messages.

CodePudding user response：

Every two minutes, decrement the certainty of all rows which have not been heard for more than two minutes.

Be sure to store the timestamps in a format that is easy to do math on. For your purposes the Unix epoch time would be preferable. Math is easy and there's no time zone issues.

Use max to ensure certainty does not drop below zero.

update sensors
set certainty = max(certainty - 0.05, 0)
where last_heard < unixepoch() - 120
  and certainty > 0

Alternatively, you can calculate the certainty at any time using a similar query and the math you suggested.

create view sensor_certainty as
select
  *,
  max(
    0.5 - (floor((unixepoch() - last_heard) / 120) * 0.05),
    0
  ) as certainty
from sensors

And, finally, you can use a generated virtual column.

alter table sensors
  add column certainty
  generated always as
  max(
    0.5 - (floor((unixepoch() - last_heard) / 120) * 0.05),
    0
  )
  virtual
  CHECK(certainty >= 0 and certainty <= 1),