Różnica między 2 datami w SQLite

Jak uzyskać różnicę w dniach między dwiema datami w SQLite? Próbowałem już czegoś takiego:

SELECT Date('now') - DateCreated FROM Payment

Za każdym razem zwraca 0.

 SELECT julianday('now') - julianday(DateCreated) FROM Payment;

Różnica w dniach

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) As Integer)

Różnica w godzinach

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 As Integer)

Różnica w minutach

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 As Integer)

Różnica w sekundach

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 * 60 As Integer)

Obie odpowiedzi dostarczają rozwiązań nieco bardziej złożonych, ponieważ muszą być. Powiedzmy, że płatność została utworzona w dniu January 6, 2013. Chcemy poznać różnicę między tą datą a dniem dzisiejszym.

sqlite> SELECT julianday() - julianday('2013-01-06');
34.7978485878557 

Różnica wynosi 34 dni. Możemy użyć julianday('now')dla lepszej przejrzystości. Innymi słowy, nie musimy wstawiać date()ani datetime()funkcjonować jako parametry do julianday() działania.

Dokumentacja SQLite to świetne źródło informacji, a strona DateAndTimeFunctions jest dobra do dodania do zakładek.

Warto również pamiętać, że korzystanie z zapytań za pomocą narzędzia wiersza poleceń sqlite jest dość łatwe:

sqlite> select julianday(datetime('now'));
2454788.09219907
sqlite> select datetime(julianday(datetime('now')));
2008-11-17 14:13:55

Ta odpowiedź jest trochę rozwlekła, a dokumentacja tego nie powie (ponieważ zakładają, że przechowujesz swoje daty jako daty UTC w bazie danych), ale odpowiedź na to pytanie zależy w dużej mierze od strefy czasowej, w której przechowywane są twoje daty in. Nie Date('now')używasz również julianday()funkcji , ale używasz tej funkcji, aby obliczyć obie daty w odniesieniu do wspólnej daty, a następnie odjąć różnicę między tymi wynikami.

Jeśli twoje daty są przechowywane w UTC:

SELECT julianday('now') - julianday(DateCreated) FROM Payment;

To właśnie zawiera najwyżej sklasyfikowana odpowiedź, a także znajduje się w dokumentacji . To tylko część obrazu i bardzo uproszczona odpowiedź, jeśli o mnie chodzi.

Jeśli Twoje daty są przechowywane w czasie lokalnym, użycie powyższego kodu spowoduje, że odpowiedź będzie NIEPRAWIDŁOWA, biorąc pod uwagę liczbę godzin przesunięcia względem GMT. Jeśli jesteś we wschodnich Stanach Zjednoczonych, tak jak ja, czyli GMT -5, Twój wynik będzie miał 5 godzin. A jeśli spróbujesz DateCreateddostosować się do UTC, ponieważ julianday('now')jest to data GMT:

SELECT julianday('now') - julianday(DateCreated, 'utc') FROM Payment;

Ma to błąd DateCreatedpolegający na tym, że doda godzinę dla czasu letniego (marzec-listopad). Powiedz, że „teraz” jest w południe w dniu innym niż czas letni, a utworzyłeś coś w czerwcu (podczas czasu letniego) w południe, a wynik będzie obejmował 1 godzinę przerwy zamiast 0 godzin dla części godzinowej. Musiałbyś napisać funkcję w kodzie aplikacji, która wyświetla wynik, aby zmodyfikować wynik i odjąć godzinę od dat czasu letniego. Robiłem to, dopóki nie zdałem sobie sprawy, że istnieje lepsze rozwiązanie tego problemu, który miałem: SQLite vs. Oracle - Obliczanie różnic w datach - godziny

Zamiast tego, jak mi wskazano, w przypadku dat przechowywanych w czasie lokalnym należy dopasować oba do czasu lokalnego:

SELECT julianday('now', 'localtime') - julianday(DateCreated) FROM Payment;

Lub dołącz 'Z'do czasu lokalnego:

julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z')

Oba wydają się rekompensować i nie dodawać dodatkowej godziny do dat czasu letniego i odejmować bezpośrednio - tak, że element utworzony w południe w dniu czasu letniego, sprawdzając w południe w dniu innym niż letni czas letni, nie otrzyma dodatkowej godziny, gdy wykonanie obliczeń.

I chociaż wiem, że większość powie, że nie przechowuj dat w czasie lokalnym w swojej bazie danych i przechowuj je w UTC, abyś tego nie spotkał, cóż, nie każda aplikacja ma odbiorców na całym świecie i nie każdy programista chce aby przejść przez konwersję KAŻDEJ daty w ich systemie na UTC iz powrotem za każdym razem, gdy wykonują GET lub SET w bazie danych i radzą sobie z ustaleniem, czy coś jest lokalne, czy w UTC.

Tylko uwaga do pisania funkcji timeclock. Dla tych, którzy szukają przepracowanych godzin, bardzo prosta zmiana powoduje, że godziny plus minuty są wyświetlane jako procent 60, tak jak chce tego większość firm płacowych.

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

Jeśli chcesz mieć czas w formacie 00:00: rozwiązałem to w ten sposób:

select strftime('%H:%M',CAST ((julianday(FinishTime) - julianday(StartTime)) AS REAL),'12:00') from something

Biorąc pod uwagę, że format daty jest następujący: „RRRR-MM-DD GG: MM: SS”, jeśli chcesz znaleźć różnicę między dwiema datami w liczbie miesięcy:

(strftime('%m', date1) + 12*strftime('%Y', date1)) - (strftime('%m', date2) + 12*strftime('%Y', date2))

Po pierwsze, nie jest jasne, jaki jest twój format daty. Jest już odpowiedź angażująca strftime("%s").

Lubię rozwinąć tę odpowiedź.

SQLite ma tylko następujące klasy pamięci: NULL, INTEGER, REAL, TEXT lub BLOB. Dla uproszczenia założę, że daty są PRAWDZIWE i zawierają sekundy od 01.01.1970. Oto przykładowy schemat, dla którego wstawię przykładowe dane z „1 grudnia 2018 r.”:

CREATE TABLE Payment (DateCreated REAL);
INSERT INTO Payment VALUES (strftime("%s", "2018-12-01"));

Teraz obliczmy różnicę dat między „1 grudnia 2018 r.” A teraz (gdy to piszę, jest południe 12 grudnia 2018 r.):

Różnica dat w dniach:

SELECT (strftime("%s", "now") - DateCreated) / 86400.0 FROM Payment;
-- Output: 11.066875

Różnica dat w godzinach:

SELECT (strftime("%s", "now") - DateCreated) / 3600.0 FROM Payment;
-- Output: 265.606388888889

Różnica dat w minutach:

SELECT (strftime("%s", "now") - DateCreated) / 60.0 FROM Payment;
-- Output: 15936.4833333333

Różnica dat w sekundach:

SELECT (strftime("%s", "now") - DateCreated) FROM Payment;
-- Output: 956195.0

Jeśli chcesz różnicy w sekundach

SELECT strftime('%s', '2019-12-02 12:32:53') - strftime('%s', '2019-12-02 11:32:53')

Jeśli chcesz nagrywać między dniami,

select count(col_Name) from dataset where cast(julianday("now")- julianday(_Last_updated) as int)<=0;

W moim przypadku muszę obliczyć różnicę w minutach i julianday()nie podaje dokładnej wartości. Zamiast tego używam strftime():

SELECT (strftime('%s', [UserEnd]) - strftime('%s', [UserStart])) / 60

Obie daty są konwertowane na unixtime (sekundy), a następnie odejmowane, aby uzyskać wartość w sekundach między dwiema datami. Następnie podziel go przez 60.

https://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions