Найдите разницу во времени в часах и минутах между отметками даты и времени в формате ГГГГ-ММ-DDTHH-ММ-SS.sssZ в SQLite или Python

#python #sqlite

Вопрос:

У меня есть серия меток даты и времени, которые мне нужны, чтобы найти разницу в часах. Проблема заключается в том, чтобы учесть разницу в днях, когда это применимо. Я могу либо попытаться сделать это на Python, когда данные добавляются в dataframes, либо в 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'),

Я не могу найти ничего, что работает. Все, что я пробовал, неправильно вычисляет различия.

1. Опубликуйте ожидаемые результаты.

2. Добавлен ожидаемый результат. Просто нужны часы и минуты между первой и второй меткой времени для каждого раздела

3. 2-я разница должна составлять 18:20, а не 19:40

Ответ №1:

Из функций даты и времени:

Функция julianday() возвращает юлианский день — количество дней, прошедших с полудня по Гринвичу 24 ноября 4714 года до н.э. (пролептический григорианский календарь).

итак, разница в вашем запросе — это разница в днях, и ее необходимо умножить на 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