SQLite DateTime-Vergleich

1. Vergleichen Sie zwei Datumsangaben mit dem Datentyp DateTime
2. Verwenden Sie die Funktion strftime(), um zwei Daten in SQLite zu vergleichen

Die SQLite-Datenbank verwendet verschiedene Arten von Daten zum Speichern, einschließlich Ganzzahlen, Zeichenfolgen und einige Datums-/Uhrzeitformate. Für die Datums-Zeit-Speicherung gibt es verschiedene Typen wie date, smalldatetime, DateTime, datetime2 usw.

In der Regel verwenden wir die WHERE-Klausel zum Abgleich in der SQLite-Datenbank und rufen die benötigten Daten ab.

Die Frage ist, wie man verschiedene Arten von Datetime-Formaten innerhalb der SQL-Datenbank vergleicht. Denken Sie daran, dass es keine direkte Möglichkeit gibt, Datum und Uhrzeit zu vergleichen.

Vergleichen Sie zwei Datumsangaben mit dem Datentyp DateTime

In SQLite gibt es keine Möglichkeit, Daten direkt zu vergleichen. Es gibt jedoch eingebaute Funktionen für Datums- und Zeitangaben in SQLite, die Datums- und Zeitangaben im Text, ganze Zahlen oder reelle Werte speichern können.

Sie sind anwendungsabhängig, und wir können das Format für integrierte Zeit- und Datumsfunktionen auswählen.

1. Text nimmt die Zeichenfolgen ISO8601 im Format JJJJ-MM-TT HH:MM:SS.SSS an.
2. Integer nimmt die Unix-Zeit, die das Format 1970-01-01 00:00:00 UTC hat.

Zum Vergleich wandeln wir also normalerweise das Datum und die Uhrzeit in einen String in einem bestimmten Format um, um es konsistent zu machen. Beispielsweise speichern wir das Datum im Format JJJJ-MM-TT hh:mm:ss.

Wenn wir Datum und Uhrzeit konsequent in diesem Format speichern, können wir Datum und Uhrzeit problemlos vergleichen. Wir können der folgenden Anweisung folgen, um ein einfaches Vergleichsbeispiel mit Datumsangaben zu demonstrieren.

SELECT * FROM name_of_the_table WHERE comparsion_date < '2020-04-05 00:00:00';

Obwohl wir sehen können, dass der Vergleich nicht numerisch ist, haben wir einen alphabetischen Vergleich. Dies ist korrekt, wenn das gespeicherte Datumsformat und das Vergleichsdatum ähnliche Formate haben.

Hier müssen wir uns an zwei wichtige Punkte erinnern.

1. Es wird empfohlen, die Zeit im 24-Stunden-Format zu speichern, da wir sie dann einfach vergleichen können.
2. Es ist nicht notwendig, Datum und Uhrzeit in einer einzelnen Spalte zu speichern, aber der wichtige Faktor ist die Einheitlichkeit des Formats. Denn wenn Datum und Uhrzeit in getrennten Spalten stehen, dann sollte das Format JJJJ-MM-TT für die Datumsspalte und hh:mm:ss für die Zeitspalte sein.

Wenn sich also Datum und Uhrzeit in separaten Spalten befinden, schreiben wir die SQL-Abfrage wie folgt. Der entscheidende Punkt ist, ein Datums- und Uhrzeitformat in der gesamten Datenbank konsistent zu machen.

SELECT * FROM name_of_the_table WHERE comparsion_date = '2020-05-03' AND comparison_time = '08:03:11';

Ein weiterer Grund, warum das Format JJJJ-MM-TT hh:mm:ss bevorzugt wird, liegt darin, dass beim Sortieren vom höchsten zum niedrigsten Index, also von Stunden zu Sekunden, der Vergleich sehr einfach ist und die Leistung gewonnen hat nicht erniedrigt werden.

Verwenden Sie die Funktion strftime(), um zwei Daten in SQLite zu vergleichen

In SQLite verwenden wir die Funktion strftime(), um das Datum in einem bestimmten Format zurückzugeben, das dem Argument übergeben wird. Das zweite Argument gibt die Zeitzeichenfolge in einem bestimmten Format zurück; der Rest sind Modifikatoren zum Abrufen von Daten mit unterschiedlichen Ergebnissen.

Die Syntax der Funktion strftime() ist wie folgt.

strftime(date_format, time_string, remaining_modifiers, ...)

Um das Datum und die Uhrzeit mit der Funktion strftime() zu vergleichen, verwenden wir die Operatoren BETWEEN und AND in der SQL-Anweisung, um ein bestimmtes Datum in einem bestimmten Zeitraum zu finden.

SELECT * FROM name_of_the_table WHERE
    strftime('%s', c_date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)

Mit der Funktion strftime() können wir die Differenz zweier datetime von der aktuellen Uhrzeit zu einer bestimmten Datumszeit ausgeben.

SELECT strftime('%s','now') - strftime('%s','2019-09-05 03:52:35');

Ausgang:

Output of strftime('%s','now') - strftime('%s','2019-09-05 03:52:35')
-----------------------------------------------------------
2010032

Konvertieren Sie DateTime in Stunden, Minuten, Sekunden und Millisekunden

Wir verwenden die Funktion strftime(), um die Einheit DateTime in Stunden, Minuten, Sekunden und Millisekunden umzuwandeln. Wir müssen die Funktion strftime() verwenden, um die folgenden Ergebnisse zu erzielen.

SELECT strftime('%H %M %S %s','now');

Ausgang:

Output of strftime('%H %M %S %s','now')
-----------------------------
16 54 04 2541572981

Berechnen Sie die Sekunden von einer bestimmten Zeit bis zur aktuellen Zeit (jetzt)

Wir verwenden auch die Funktion strftime(), um den Datentyp DateTime in Sekunden umzuwandeln und die verbleibenden Sekunden von den bereitgestellten Argumenten bis zur aktuellen Zeit in Sekunden zu berechnen.

SELECT strftime('%s','now') - strftime('%s','2019-01-04 01:25:47');

Ausgang:

Output of strftime('%s','now') - strftime('%s','2019-01-04 01:25:47')
-----------------------------------------------------------
3221184

Berechnen Sie den Zeitverlauf in Jahren, Monaten und Tagen

Nehmen Sie ein einfaches Beispiel für den Jobwartungsverlauf, bei dem es eine Tabelle mit dem Start- bis Enddatum des Jobs gibt und der Benutzer den gesamten Zeitverlauf in Jahren, Monaten und Tagen berechnen möchte.

Hier ist das Tabellenformat mit drei Spalten: emp_id, start_job_date und end_job_date. Der Datentyp von emp_id ist ganzzahlig, während die restlichen Spalten start_job_date und end_job_date DateTime sind.

Um die Differenz zwischen start_job_date und end_job_date aus der obigen Tabelle mithilfe der SQL-Abfragen zu berechnen, verwenden wir die Funktion strftime(), um direkt die Differenz in Jahren, Monaten und Tagen zu erhalten.

SELECT strftime('%Y',end_job_date - start_job_date) as "Difference in years",
strftime('%m',end_job_date - start_job_date) as "Difference in months",
strftime('%d',end_date - start_job_date) as "Difference in days"
FROM job_history;

Ausgang: