sqlite - 以毫秒精度减去和聚合日期

Question

我有一个包含日期时间的两列的表，特别是 time_started和 time_ended .我想确定这两次定义的持续时间并将所有持续时间相加。

这是我的尝试:

select strftime('%H:%M:%S.%f', sum(duration), 'unixepoch') 
from (
    select strftime('%s', time_ended) - strftime('%s', time_started) as duration
    from mytable
)

这不起作用的原因是 strftime('%s', time_started)返回 seconds since 1970-01-01 的整数.这意味着我正在失去毫秒。

有什么办法可以得到 小数 自 1970-01-01 以来的秒数？

请注意，日期时间值的格式与此示例相同:“2013-04-24 14:57:30.661259”。

更新 , 现在使用 julianday() ，通过@LS_dev 在答案中的建议:

select strftime('%H:%M:%f', sum(duration)) 
from (
    select julianday(time_ended) - julianday(time_started) as duration
    from mytable
)

但仍然存在两个问题:

总和的结果比应有的多 12 小时(!)

毫秒被截断为三位数，这看起来像是 strftime() 的限制

Answer 1

使用JULIANDAY :

select duration*24*60*60
from (
    select JULIANDAY(time_ended) - JULIANDAY('%s', time_started) as duration
    from mytable
)

编辑:“为您完成所有工作”的解决方案:

SELECT *, TIME("00:00", CAST(Duration AS INT)||' seconds')||SUBSTR(Duration-CAST(Duration AS INT), 2)
FROM (
    SELECT *, (JULIANDAY(time_ended) - JULIANDAY(time_started))*60*60*24 as Duration
    FROM mytable
)

签到 SQL Fiddle