使用时间戳计算总持续时间

Question

在我的SQLite表中，我有以下字段(timestamp、zone_id、object_count和object_ids(，这些是在区域中排队的人的数据。每当一个人加入队列时，传感器就会捕捉到他，并为他分配一个object_id。timestamp是传感器服务器每秒向数据库发送的数据。

我需要找到队列中每个object_ids的持续时间，以便使用flask框架在我的web应用程序中计算和显示等待时间。例如，object_ids(2166(在2020-10-19 17:03:46.000000进入队列，在2020-10-19 17:03:50.000000离开，他的队列时间为5秒。简单的逻辑是从object_id最后出现的时间戳中减去object_id第一次出现时的时间戳。

#       timestamp           zone_id   object_count      object_ids #  
2020-10-19 17:03:43.000000    10           2         ['2140', '2143']
2020-10-19 17:03:44.000000    10           2         ['2140', '2143']
2020-10-19 17:03:45.000000    10           2         ['2140', '2143']
2020-10-19 17:03:46.000000    10           3         ['2140', '2143', '2166']
2020-10-19 17:03:47.000000    10           3         ['2140', '2143', '2166']
2020-10-19 17:03:49.000000    10           3         ['2140', '2143', '2166']
2020-10-19 17:03:50.000000    10           3         ['2140', '2143', '2166']
2020-10-19 17:03:51.000000    10           2         ['2140', '2143']
2020-10-19 17:03:53.000000    10           2         ['2140', '2143']

如何查询和解决此问题？

Answer 1

假设表的名称是queues，定义object_id的表是objects(将名称更改为实际名称(，那么您就有了这样的东西：

CREATE table objects (`object_id` TEXT);
INSERT INTO objects (`object_id`) VALUES
('2140'), ('2143'), ('2166');
CREATE TABLE queues (`timestamp` TEXT, `zone_id` INTEGER, `object_count` INTEGER, `object_ids` VARCHAR(24));
INSERT INTO queues (`timestamp`, `zone_id`, `object_count`, `object_ids`) VALUES
('2020-10-19 17:03:43.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:44.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:45.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:46.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:47.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:49.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:50.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:51.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:53.000000', '10', '2', '[''2140'', ''2143'']');

通过使用窗口函数MIN()和MAX()以及函数strftime():，可以通过连接这两个表的SQLite查询获得所需的结果

SELECT DISTINCT o.object_id,
strftime('%s', MAX(q.timestamp) OVER (PARTITION BY o.object_id)) - 
strftime('%s', MIN(q.timestamp) OVER (PARTITION BY o.object_id)) diff
FROM objects o INNER JOIN queues q
ON ',' || REPLACE(REPLACE(REPLACE(REPLACE(q.object_ids, '''', ''), '[', ''), ']', ''), ' ', '') || ','
LIKE '%,' || o.object_id || ',%'

如果您的SQLite版本不支持窗口函数，请使用GROUP BY:

SELECT o.object_id,
strftime('%s', MAX(q.timestamp)) - strftime('%s', MIN(q.timestamp)) diff
FROM objects o INNER JOIN queues q
ON ',' || REPLACE(REPLACE(REPLACE(REPLACE(q.object_ids, '''', ''), '[', ''), ']', ''), ' ', '') || ','
LIKE '%,' || o.object_id || ',%'
GROUP BY o.object_id

请参阅演示
结果：

> object_id | diff
> :-------- | ---:
> 2140      |   10
> 2143      |   10
> 2166      |    4

Answer 2

因此，在您的应用程序.py中，为了获得正确的数据库，您应该有这些代码，这些代码应该在文件中的某个位置，这样您的所有函数都可以访问它。

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

然后在你想要时间的函数下面的某个地方，你应该有这样的

timestamp = db.execute(text("SELECT timestamp FROM NAME_OF_YOUR_TABLE").fetchall()#this will get you all of the timestamps

现在，您已经在python中的表中获得了时间戳，因此您可以轻松地对其执行任何操作，可以轻松地将其转换为datetime格式，然后只需使用datetime.datetime.Now((函数并将其相互减去，如果您还需要其他内容，请随时在下面发表评论。

编辑：所以为了循环通过ID列，你需要这样的东西：

T1 = db.execute(text("SELECT object_ids FROM NAME_OF_YOUR_TABLE")).fetchall()
for i in T1:

timestamp = db.execute(text("SELECT timestamps FROM NAME_OF_YOUR_TABLE WHERE object_ids=:object_ids"), {'object_ids':i}).fetchone()
print('Timestamp = ' + timestamp 'and id = ' + i)