Как мне получить разницу между двумя датами в SQLite? Я уже пробовал что-то вроде этого:
SELECT Date('now') - DateCreated FROM Payment
Он возвращает 0 каждый раз.
SELECT julianday('now') - julianday(DateCreated) FROM Payment;
Оба ответа предоставляют решения немного сложнее, поскольку они
должны быть. Скажем, платеж был создан на January 6, 2013.
И мы хотим знать разницу между этой датой и сегодня.
sqlite> SELECT julianday() - julianday('2013-01-06');
34.7978485878557
Разница составляет 34 дня. Мы можем использовать julianday('now') для
лучшая ясность. Другими словами, нам не нужно
date() или datetime() функционирует как параметры для julianday()
функция.
Разница в днях
Select Cast ((
JulianDay(ToDate) - JulianDay(FromDate)
) As Integer)
Разница в часах
Select Cast ((
JulianDay(ToDate) - JulianDay(FromDate)
) * 24 As Integer)
Разница в минутах
Select Cast ((
JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 As Integer)
Разница в секундах
Select Cast ((
JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 * 60 As Integer)
SQLite wiki – отличная ссылка, а страница DateAndTimeFunctions является хорошей для закладки. Также полезно помнить, что довольно легко играть с запросами с помощью утилиты командной строки sqlite:
sqlite> select julianday(datetime('now'));
2454788.09219907
sqlite> select datetime(julianday(datetime('now')));
2008-11-17 14:13:55
Этот ответ немного длинный, и документация не скажет вам об этом (потому что они предполагают, что вы сохраняете свои даты в качестве дат UTC в базе данных), но ответ на этот вопрос во многом зависит от часового пояса, даты сохраняются. Вы также не используете Date('now'), но используете функцию julianday(), чтобы рассчитать оба дате назад к общей дате, а затем вычесть разницу этих результатов друг от друга.
Если ваши даты хранятся в UTC:
SELECT julianday('now') - julianday(DateCreated) FROM Payment;
Это то, что имеет верхний рейтинг, и также находится в документации. Это только часть картины, и очень упрощенный ответ, если вы спросите меня.
Если ваши даты хранятся в местное время, использование вышеуказанного кода сделает ваш ответ WRONG на количество часов вашего смещения по GMT. Если вы находитесь в Восточном штате США, как я, то есть GMT-5, ваш результат будет содержать 5 часов. И если вы попытаетесь сделать DateCreated совместимым с UTC, потому что julianday('now') идет против даты GMT:
SELECT julianday('now') - julianday(DateCreated, 'utc') FROM Payment;
У этого есть ошибка, в которой он добавит час для DateCreated, который находится во время летнего времени (март-ноябрь). Скажите, что “сейчас” в полдень в день, не относящийся к DST, и вы создали что-то еще в июне (во время DST) в полдень, ваш результат даст 1 час, а не 0 часов, для части часов. Вам нужно будет написать функцию в вашем коде приложения, которая отображает результат, чтобы изменить результат и вычесть час из дат DST. Я сделал это, пока не понял, что есть лучшее решение этой проблемы: SQLite vs. Oracle – вычисление различий по дате – часы
Вместо этого, как указывалось мне, для дат, хранящихся в локальное время, сделать оба совпадения локальными:
SELECT julianday('now', 'localtime') - julianday(DateCreated) FROM Payment;
Или добавьте 'Z' в локальное время:
julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z')
Оба эти, кажется, компенсируют и не добавляют лишний час для дат DST и делают прямое вычитание – так что элемент, созданный в полдень в день DST, при проверке в полдень в день, не относящийся к DST, не получит дополнительный час при выполнении расчета.
И хотя я признаю, что большинство из них скажут, что не хранят даты по местному времени в вашей базе данных и хранить их в UTC, чтобы вы не столкнулись с этим, не все приложения имеют всемирную аудиторию, а не каждый программист хочет пройти конверсию КАЖДОЙ даты в своей системе в UTC и вернуться снова каждый раз, когда они делают GET или SET в базе данных и разбираются, если что-то локально или в UTC.
Просто примечание для написания функций тайм-слова. Для тех, кто ищет часы работы, очень простое изменение получило часы, а минуты показываются в процентах от 60, так как большинство компаний, имеющих начисление заработной платы, хотят этого.
CAST ((julianday(clockOUT) - julianday(clockIN)) * 24 AS REAL) AS HoursWorked
Clock In Clock Out HoursWorked
2016-08-07 11:56 2016-08-07 18:46 6.83333332836628
Если вы хотите время в формате 00:00:
Я решил это так:
select strftime('%H:%M',CAST ((julianday(FinishTime) - julianday(StartTime)) AS REAL),'12:00') from something
Если вы хотите записи между днями,
select count(col_Name) from dataset where cast(julianday("now")- julianday(_Last_updated) as int)<=0;
Учитывая, что ваш формат даты следующий: “ГГГГ-ММ-ДД ЧЧ: ММ: СС”, если вам нужно найти разницу между двумя датами в количестве месяцев:
(strftime('%m', date1) + 12*strftime('%Y', date1)) - (strftime('%m', date2) + 12*strftime('%Y', date2))
В моем случае я должен рассчитать разницу в минутах, а julianday() не дает точного значения. Вместо этого я использую strftime():
SELECT strftime('%s', [UserEnd]) - strftime('%s', [UserStart]))/60
Обе даты преобразуются в unixtime (секунды), а затем вычитаются, чтобы получить значение в секундах между двумя датами. Затем разделите это на 60.
Во-первых, не ясно, какой у вас формат даты. Там уже есть ответ с участием strftime("%s").
Мне нравится расширять этот ответ.
SQLite имеет только следующие классы хранения: NULL, INTEGER, REAL, TEXT или BLOB. Чтобы упростить вещи, я собираюсь предположить, что даты НАСТОЯЩИЕ содержат секунды с 1970-01-01. Вот пример схемы, для которой я добавлю пример данных “1 декабря 2018 года”:
CREATE TABLE Payment (DateCreated REAL);
INSERT INTO Payment VALUES (strftime("%s", "2018-12-01"));
Теперь давайте определимся с разницей в датах между “1 декабря 2018 года” и сейчас (сейчас, когда я пишу, это полдень 12 декабря 2018 года):
Разница в днях:
SELECT (strftime("%s", "now") - DateCreated) / 86400.0 FROM Payment;
-- Output: 11.066875
Разница дат в часах:
SELECT (strftime("%s", "now") - DateCreated) / 3600.0 FROM Payment;
-- Output: 265.606388888889
Разница дат в минутах:
SELECT (strftime("%s", "now") - DateCreated) / 60.0 FROM Payment;
-- Output: 15936.4833333333
Разница в дате в секундах:
SELECT (strftime("%s", "now") - DateCreated) FROM Payment;
-- Output: 956195.0