Как правильно подсчитать разницу во времени, SQLite?

Question

[Ledauph1]

Имеется БД:

CREATE TABLE user_sessions (
    id         INTEGER PRIMARY KEY AUTOINCREMENT
                       UNIQUE
                       NOT NULL,
    user_id            REFERENCES user (user_id) ON DELETE CASCADE
                                                 ON UPDATE CASCADE
                       NOT NULL,
    date_start DATE    NOT NULL
                       DEFAULT (DATE() ),
    time_start TIME    NOT NULL
                       DEFAULT (time('now', 'localtime') ),
    date_end   DATE    DEFAULT NULL,
    time_end   TIME    DEFAULT NULL
);

Пример данных в БД

Необходимо подсчитать разницу во времени (date_end - data_start), выполняю команду:

SELECT  id, date_start, user_id, time_start, time_end, STRFTIME("%H:%M:%S", (julianday(time_end) - julianday(time_start)))  AS "time session"
FROM user_sessions

Результат:

Получаю результат на 12 часов больше.

Ожидаемый результат:

Comments

[сергей кузьмин]

ДА ИНТЕРЕСНО КАК ТО НАДО по другому вызывать 12 часов теряет

drop table IF EXISTS _Variables;
CREATE TEMP TABLE _Variables(date_end date, time_end time, date_start date, time_start time) ;
INSERT INTO _Variables VALUES ('2022-01-21', '13:00:00', '2022-01-21', '01:00:00');
select STRFTIME("%H:%M:%S", julianday(datetime(date_end,  time_end )) - julianday(datetime(date_start,  time_start ))) from _variables;

00:00:00

[Ledauph1]

сергей кузьмин, что-то ничего не могу придумать, разве "вручную" средствами python)

[сергей кузьмин]

почему

select  CAST( strftime('%s',datetime(date_end,  time_end ) )  AS LONG ), CAST( strftime('%s',datetime(date_start,  time_start ) ) AS LONG) from _variables;

select  STRFTIME("%H:%M:%S",  CAST( strftime('%s',datetime(date_end,  time_end ) )  AS LONG ) - CAST( strftime('%s',datetime(date_start,  time_start ) ) AS LONG) )from _variables;

12:00:00
если помогло - я положу в ответы вы отметите решением

[Ledauph1]

сергей кузьмин, Что-то не получается...

spoiler

[сергей кузьмин]

Ledauph1, ну с ошибками вы разберетесь сами думаю переменные не использовали как у вас там столбцы называются я пристально не смотрел

"сухой итог":
juliandayиспользовать не рекомендуется а epoch(a.k.a. strfime "%s" все работает так как epoch это секунды с 01.01.1970

[Ledauph1]

Спасибо, это работает, в секундах все показывает правильно.
сергей кузьмин,

select  CAST( strftime('%s',datetime(date_end,  time_end ) )  AS LONG ), CAST( strftime('%s',datetime(date_start,  time_start ) ) AS LONG) from _variables;

Здесь STRFTIME работает не правильно:

select  STRFTIME("%H:%M:%S",  CAST( strftime('%s',datetime(date_end,  time_end ) )  AS LONG ) - CAST( strftime('%s',datetime(date_start,  time_start ) ) AS LONG) )from _variables;

spoiler

В целом опубликуйте ответ, и я отмечу его, верным решением.
Спасибо.

Answer 1

[сергей кузьмин]

если надо вычислить временной интервал
напр
между date_end, time_end
и date_start, time_start
то надо в datetimeпотом в epochпотом castв long, вычесть и отформатировать

select  STRFTIME("%H:%M:%S",  CAST( strftime('%s',datetime(date_end,  time_end ) )  AS LONG ) - CAST( strftime('%s', datetime(date_start,  time_start ) ) AS LONG) ) from _variables;

в примере сделана временная таблица с колонками

drop table IF EXISTS _Variables;
CREATE TEMP TABLE _Variables(date_end date, time_end time, date_start date, time_start time) ;
INSERT INTO _Variables VALUES ('2022-01-21', '13:00:00', '2022-01-21', '01:00:00');

Comments

[Bkhm]

Вы упускаете из виду одну деталь,
начало отсчета для Julianday.

...julian day number (corresponding to dates between -4713-11-24 12:00:00 and...

Answer 2

[rPman]

У меня совет, он не абсолютный конечно, но в большинстве случаев экономит время нервы и работает ЗНАЧИТЕЛЬНО эффективнее (меньше тратит процессорное время как бакэнд так и сервера базы данных)

храните время в виде числа timestamp - количество секунд, по ситуации, в большинстве случаев хватает unixtime, очень редко может потребоваться хранить 64-битный long количества миллисекунд (timestamp*1000).
Исторические даты (старее 1 января 1970) да, лучше хранить в формате, понимаемом базой данных (если нужно делать сравнение < >, сортировку и другие операции).

Большинство преобразований и работа может быть выполнена на бакэнде, зачастую на сервере вычисления и сравнения можно делать в секундах, а граничные значения подготавливать на бакэнде.

Неужели вам самим не корежит постоянное преобразование строкового представления даты туда сюда? А потом где-нибудь ошибетесь с часовым поясом и вылезут ошибки такого вида как у автора вопроса.

p.s. у sqlite нет типа date (там как я понимаю строка) но есть функции работы со временем
https://www.sqlite.org/datatype3.html#date_and_tim...
https://www.sqlite.org/lang_datefunc.html

на самом деле там веселее

sqlite сохраняет данные в том типе, который вы попытаетесь записать, когда вы пишете тип date то это ничего не значит, и если дальше записать строку с датой, запишется строка, если число - будет число, дальнейшая работа зависит от того какую функцию скормишь этим данным

если проведете бенчмарки, все это великолепие работает отвратительно, и имеет смысл когда в консоли sqlite делаешь анализ, и все эти now, before, 'one day' очень все облегчают, но когда пишешь код, проще работать с понятными секундами, для которых на других языках разработано огромное количество хелперов

Comments

[Ledauph1]

Спасибо, дельный совет.
Подтолкнули на мысль, для моей цели, достаточно получить, результат в секундах.