在SQLite或Python DDTHH SS.sssZ格式的日期时间戳之间查找以小时和分钟为单位的时间差

Question

我有一系列的日期时间戳，我需要找出几个小时的差异。问题是，在适用的情况下，让它考虑到日间的差异。当数据被添加到dataframes时，我可以尝试用Python来完成，或者在分析数据时用SQL进行尝试。这两种方法都有效。

用于测试的SQL代码：

SELECT commentId, commentStamp, replyStamp, 
       strftime('%H:%M:%SZ',(julianday(replyStamp) - julianday(commentStamp))) 
FROM time_table

输出：

('AAAAA00000',
  '2020-11-10T13:26:10.162Z',
  '2020-11-10T13:35:37.292Z',
  '12:09:27Z'),
 ('AAAAA11111',
  '2020-11-09T18:48:03.073Z',
  '2020-11-10T13:08:22.813Z',
  '06:20:19Z'),

预期的结果会是这样的，只需要几个小时和几分钟：

('AAAAA00000',
  '2020-11-10T13:26:10.162Z',
  '2020-11-10T13:35:37.292Z',
  '00:09'),
 ('AAAAA11111',
  '2020-11-09T18:48:03.073Z',
  '2020-11-10T13:08:22.813Z',
  '18:40'),

我真的找不到有用的东西。我试过的每一件事都不能正确地计算出差异。

Answer 1

来自日期和时间函数

函数返回朱利安日(自公元前4714年11月24日格林威治正午以来的天数)。

因此，查询中的差异是以天为单位的差异，必须乘以24 * 60 * 60才能得到以秒为单位的差异，并与strftime()一起使用它将其格式化为时间(使用'unixepoch'修饰符)：

SELECT commentId, commentStamp, replyStamp, 
       strftime('%H:%M', (julianday(replyStamp) - julianday(commentStamp)) * 24 *60 * 60, 'unixepoch') diff
FROM time_table

或者使用strftime('%s', ...)返回自1970-01-01的秒数：

SELECT commentId, commentStamp, replyStamp, 
       strftime('%H:%M', strftime('%s', replyStamp) - strftime('%s', commentStamp), 'unixepoch') diff
FROM time_table

见演示。

结果：

> commentId  | commentStamp             | replyStamp               | diff 
> :--------- | :----------------------- | :----------------------- | :----
> AAAAA00000 | 2020-11-10T13:26:10.162Z | 2020-11-10T13:35:37.292Z | 00:09
> AAAAA11111 | 2020-11-09T18:48:03.073Z | 2020-11-10T13:08:22.813Z | 18:20