从数据库中获取每个n小时数据的容忍度 [英] Get every n-hour data from database with tolerance

问题描述

我有一个MySQL数据库，每10分钟在其中写入一次数据.即:

I have a MySQL database in which I write data every 10 minutes. i.e.:

+---------------------+
| datetime            |
+---------------------+
| 2018-09-03 13:01:49 |
| 2018-09-03 12:51:49 |
| 2018-09-03 12:41:49 |
+---------------------+

在我的Python代码中，我只想获取完全" n小时前的行，即:

In my Python code I want to get just rows which are "exactly" n-hour old, i.e.:

+---------------------+
| datetime            |
+---------------------+
| 2018-09-03 13:01:49 |
| 2018-09-03 12:01:49 |
| 2018-09-03 11:01:49 |
| 2018-09-03 10:01:49 |
| 2018-09-03 09:01:49 |
| 2018-09-03 08:01:49 |
+---------------------+

我有这段代码可以返回我想要的数据:

I have this code which returns data I want:

cursor.execute('SELECT max(datetime) FROM temperatures')
last_record_datetime = cursor.fetchone()
last_record_min = last_record_datetime[0].minute

query = f'SELECT * FROM temperatures WHERE DATETIME LIKE "%:%{last_record_min}:%" ORDER BY ID DESC LIMIT 20'
cursor.execute(query)
query_result = cursor.fetchall()

这里是我的问题:如果我将重新引导系统，或者出现一些问题或延迟，并且最后一条记录的日期时间和最后一条记录之前的记录的分钟数将不对应，我将从数据库中得到空答复(因为查询 ... LIKE"22" 与查询 ... LIKE"21" 不匹配.)

And here comes my question: If I will reboot the system, or there will be some problem or delay, and minutes in datetime of last record and records before the last one will not corresponding, I will get empty reply from database (because query ... LIKE "22" not match with query ... LIKE "21").

那么，从数据库中获取具有一定容忍度(例如+ -4.99分钟)的数据的最佳方法是什么?

So, what is the best way to get data from the database with some tolerance (let's say +- 4,99 min)?

推荐答案

如果您大约每10分钟写入一次数据，则意味着您要在按 datetime 进行排序时获取第6行.

If you write your data approximately every 10 minutes, it means that you want to fetch every 6th row, when you order by datetime.

您可以尝试以下方法:

select @rn := 1;

select `datetime` from (
    select @rn := @rn + 1 rn, `datetime` from temperatures
    order by `datetime` desc
) where mod(rn, 6) = 1
--limit 20

这是另一种方法，它将计算日期时间与最近日期相距整整一小时"的时间，并根据该日期进行过滤(允许时间相差5分钟):

This is alternative approach, it will calculate how close datetime is to be"full hour" away from your latest date and it will filter based on that (it allows times to vary by 5 minutes):

select @dt := max(`datetime`) from temperatures;

select * from (
    select `datetime`, 
           mod(abs(timestampdiff(minuite,@dt,`datetime`)), 60) minDiff
    from temperatures
) a where minDiff < 5 or minDiff > 55
--limit 20

这篇关于从数据库中获取每个n小时数据的容忍度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！