分组时间范围的sqlite查询

Question

我正在尝试将我的连接历史记录表查询到每小时的时间范围内，每小时的连接持续时间。

create table sessionHistory (
  _id       integer PRIMARY KEY autoincrement,
  modified  integer,
  ip        text,
  ...
  started   integer,
  ended     integer
);

每个连接开始都有一个“开始”日期，当它完成时，我放置了一个“结束”日期，都在 unix epoch 中。

我可以使用以下查询对它们进行分组:

select strftime('%H', started, 'unixepoch') HOUR_, 
       sum(ended-started) TOTAL_TIME_ 
from sessionHistory 
where ip='123.123.123.123' and ended!=0
group by strftime('%H', started, 'unixepoch')

现在，我还希望在每小时中包含仍在进行中的 session 时间(结束 = 0)。

例如，在第 1 小时，连接开始并结束，持续时间为 35 秒。在第 2 小时，连接在进入第 3 小时之前开始，距离转弯还有 10 秒，在进入第 3 小时后 10 秒停止。

这意味着查询需要返回类似于小时 1 => 35、小时 2 => 10、小时 3 => 10 的内容。
此外，如果连接仍然正常(结束 = 0)，我希望当前小时保存“现在”开始的数据，这将在相对小时内累积。

加入 2 个 sqlite 查询不会回答最后一个规范，因为它总是会返回“now”-“started”，即使 session 超过 1 小时。

Answer 1

select strftime('%H', started, 'unixepoch') HOUR_, 
       sum(ended-started)+
             (
             SELECT sum(now-started)
             from sessionHistory 
             where ip='123.123.123.123' and ended=0
             group by strftime('%H', started, 'unixepoch')
             ) TOTAL_TIME_ 
from sessionHistory 
where ip='123.123.123.123' and ended!=0
group by strftime('%H', started, 'unixepoch')