如何使用作为当前时间函数的值更新SQLite列

jecbmhm3 于 2023-01-02 发布在 SQLite

关注(0)|答案(1)|浏览(141)

我有一个物联网项目，它通过MQTT订阅传感器数据，将数据保存到本地SQLite3 DB，对数据进行一些处理，然后每隔60秒通过MQTT发布一些新消息。我有一个SQLite表，其模式如下所示（我省略了不相关的列）：

- 传感器**

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

表中的每一行代表一个唯一的传感器。首次听到传感器时，certainty初始化为.5。对于is_monitored为True的所有传感器，每隔两分钟未听到传感器，certainty需要递减.05。
换句话说，对于被监控的传感器，
确定性=确定性-（.05 *（（当前时间分钟-上次听到的分钟）//2））
如何在Python和/或SQLite3中最好地实现这一点？非常感谢任何帮助。
以下是我的尝试：

我尝试在数据库中创建一个视图，以便拥有一个计算列，该列保存每个传感器/行不可见的时间量，这允许我计算所谓的"adjusted_certainty"作为视图中的另一列。这种方法的问题是，您无法在SQLite中的视图中对计算列施加约束，因此adjusted_certainty将低于0。这迫使我跟踪Python代码中两个版本的确定性--第一个是传感器表中的可写确定性，第二个是视图中的只读adjusted_certainty。
在我的Python脚本中，我能够获得所有被监视的传感器，计算它们中的每一个多久没有被看到，并相应地更新它们的确定性。问题是，我不知道 * 何时 * 这样做。如果我在向MQTT发送外发消息之前这样做，似乎会发生奇怪的竞争条件，数据库、我的外发消息、或者两者都有。如果我在之后更新确定性，那么它们在发出的消息中就不准确了。

sqlite

来源：https://stackoverflow.com/questions/74965536/how-to-update-a-sqlite-column-with-a-value-that-is-a-function-of-the-current-tim

1条答案

按热度按时间

polkgigr1#

每隔两分钟，降低超过两分钟未听到的所有行的确定性。
确保以易于计算的格式存储时间戳。对于您的目的，Unix纪元时间将是更好的。计算很容易，没有时区问题。
使用max确保确定性不低于零。

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

或者，您可以随时使用类似的查询和您建议的数学计算确定性。

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

最后，您可以使用生成的虚拟列。

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),

赞(0）回复(0）举报 2023-01-02

我来回答

如何使用作为当前时间函数的值更新SQLite列

1条答案

相关问题

热门标签

最新问答