So arbeiten Sie effektiv mit Daten und Zeiten in MySQL

Februar 11, 2021 Eskere Guru

Daten und Zeiten sind wichtig, sie helfen, die Dinge zu organisieren und sind ein wesentlicher Aspekt jedes Softwarebetriebs.

Die effiziente Arbeit mit ihnen in der Datenbank kann manchmal verwirrend erscheinen, unabhängig davon, ob sie über die verschiedenen Zeitzonen hinweg funktioniert, Datumsangaben hinzufügt / entfernt und andere Vorgänge ausgeführt werden.

Lernen Sie die verschiedenen verfügbaren MySQL-Funktionen kennen, mit denen Sie Datums- und Uhrzeitangaben in Ihrer Datenbank einfach handhaben und verwalten können.

Arbeiten mit Zeitzonen

Um die Standardisierung zu gewährleisten, sollten Sie immer nur mit Datums- und Uhrzeitangaben in der UTC-Zeitzone arbeiten. Jedes Mal, wenn Sie eine Verbindung zur MySQL-Datenbank herstellen, sollten Sie die Zeitzone auf UTC umstellen. Dies kann mit der folgenden SQL-Anweisung erfolgen:

 SET TIME_ZONE = '+0:00'

Da jetzt alle Daten in UTC gespeichert werden, wissen Sie immer, womit Sie arbeiten, was die Dinge einfacher und unkomplizierter macht.

Bei Bedarf können Sie die Zeitzone eines beliebigen Datums- / Zeitstempelwerts einfach mit der praktischen MySQL-Funktion CONVERT_TZ () konvertieren . Sie müssen zuerst den Offset kennen, z. B. PST an der Westküste Nordamerikas ist UTC -08: 00, also können Sie Folgendes verwenden:

 SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

Dies führt zu 2021-02-04 13:47:23, was genau richtig ist. Die drei an CONVERT_TZ () übergebenen Argumente sind zuerst das Datum / die Uhrzeit / der Zeitstempel, mit dem Sie beginnen (verwenden Sie now () für die aktuelle Zeit), das zweite ist immer '+0: 00', da alle Daten in der Datenbank auf UTC gezwungen werden und der letzte ist der Versatz, in den wir das Datum konvertieren möchten.

Daten hinzufügen / subtrahieren

Oft müssen Sie Daten hinzufügen oder von ihnen subtrahieren, z. B. wenn Sie Datensätze von vor einer Woche abrufen oder in einem Monat etwas planen müssen .

Zum Glück verfügt MySQL über die hervorragenden Funktionen DATE_ADD () und DATE_SUB () , die diese Aufgabe extrem einfach machen. Mit der SQL-Anweisung können Sie beispielsweise zwei Wochen vom aktuellen Datum abziehen:

 SELECT DATE_SUB(now(), interval 2 week);

Wenn Sie stattdessen einem vorhandenen Zeitstempel drei Tage hinzufügen möchten, verwenden Sie:

 SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Beide Funktionen funktionieren gleich, das erste Argument ist der Zeitstempel, mit dem Sie beginnen, und das zweite Argument ist das Intervall zum Addieren oder Subtrahieren. Das zweite Argument wird immer gleich formatiert, beginnend mit dem Wortintervall, gefolgt von einem numerischen Wert und dem Intervall selbst, das eines der folgenden sein kann: Sekunde, Minute, Stunde, Tag, Woche, Monat, Quartal, Jahr.

Wenn Sie beispielsweise alle Anmeldungen abrufen möchten, die in den letzten 34 Minuten aufgetreten sind, können Sie eine SQL-Anweisung verwenden, z.

 SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Wie Sie sehen können, werden dadurch alle Datensätze aus der Anmeldetabelle mit einem Anmeldedatum abgerufen, das größer als die aktuelle Zeit minus 45 Minuten oder mit anderen Worten die letzten 45 Minuten ist.

Holen Sie sich den Unterschied zwischen Daten

Manchmal müssen Sie herausfinden, wie viel Zeit zwischen zwei Daten vergangen ist. Mit der DATEDIFF- Funktion können Sie ganz einfach die Anzahl der Tage zwischen zwei verschiedenen Daten ermitteln , z. B. die folgende SQL-Anweisung:

 SELECT DATEDIFF(now(), '2020-12-15');

Die DATEDIFF- Funktion verwendet zwei Argumente, die beide Datums- / Zeitstempel sind, und gibt die Anzahl der Tage zwischen ihnen an. Das obige Beispiel zeigt die Anzahl der Tage, die vom 15. Dezember 2020 bis heute vergangen sind.

Um die Anzahl der Sekunden zwischen zwei Daten zu ermitteln, kann die Funktion TO_SECONDS () nützlich sein, zum Beispiel:

 SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Dies ergibt die Anzahl der Sekunden zwischen den beiden angegebenen Daten.

Segmente aus Daten extrahieren

Es gibt verschiedene MySQL-Funktionen, mit denen Sie bestimmte Segmente einfach aus Datumsangaben extrahieren können, z. B. wenn Sie nur den Monat, den Tag des Jahres oder die Stunde möchten. Hier einige Beispiele für solche Funktionen:

 SELECT MONTH('2021-02-11 15:27:52');

 SELECT HOUR(now());

 SELECT DAYOFYEAR('2021-07-15 12:00:00');

Die obigen SQL-Anweisungen würden zu 02 , der aktuellen Stunde, und 196 führen, da der 15. September der 196. Tag des Jahres ist. Hier ist eine Liste aller verfügbaren Datumsextraktionsfunktionen, die jeweils nur ein Argument enthalten. Das Datum wird extrahiert aus:

 - SECOND()
 - MINUTE()
 - HOUR()
 - DAY()
 - WEEK() - Number 0 - 52 defining the week within the year.
 - MONTH()
 - QUARTER() - Number 1 - 4 defining the quarter of the year.
 - YEAR()
 - DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
 - LAST_DAY() - The last day in the given month.
 - DATE() - The date in YYYY-MM-DD format without the time.
 - TIME() The time in HH:II:SS format without the date.
 - TO_DAYS() - The number of days since AD 0.
 - TO_SECONDS() - The number of seconds since AD 0.
 - UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Wenn Sie beispielsweise nur den Monat und das Jahr abrufen möchten, in denen alle Benutzer erstellt wurden, können Sie eine SQL-Anweisung verwenden, z.

 SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Dies würde alle Datensätze in der Benutzertabelle abrufen und die ID #, den Monat und das Jahr anzeigen, in denen jeder Benutzer erstellt wurde.

Gruppieren von Datensätzen nach Datum Zeitraum

Eine hervorragende Verwendung von Datumsfunktionen ist die Möglichkeit, Datensätze mithilfe von GROUP BY in Ihren SQL-Anweisungen nach Datumszeitraum zu gruppieren. Vielleicht möchten Sie beispielsweise den Gesamtbetrag aller Bestellungen im Jahr 2020 nach Monaten gruppieren. Sie können eine SQL-Anweisung verwenden, z.

 SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

Dies würde alle Bestellungen abrufen, die im Jahr 2020 aufgegeben wurden, sie nach dem Monat ihrer Erstellung gruppieren und 12 Datensätze zurückgeben, in denen der Gesamtbetrag angegeben ist, der jeden Monat des Jahres bestellt wurde.

Bitte beachten Sie, dass es für eine bessere Indexleistung immer am besten ist, Datumsfunktionen wie YEAR () in der WHERE-Klausel von SQL-Anweisungen nicht zu verwenden und stattdessen den Operator BETWEEN zu verwenden , wie im obigen Beispiel gezeigt.

Nie wieder mit Daten verwechselt werden

Mit den oben genannten Kenntnissen können Sie jetzt effizient mit Datums- und Uhrzeitvorgängen in einer Vielzahl von Anwendungsfällen arbeiten, diese übersetzen und ausführen.

Denken Sie daran, der Einfachheit halber immer UTC zu verwenden, wenn Sie mit Datumsangaben arbeiten, und verwenden Sie die oben genannten Tipps, um Datumsangaben in Ihrer Software effizient zu verwalten, unabhängig davon, ob einfache Berechnungen durchgeführt oder Berichte nach Datumszeiträumen gruppiert werden sollen.

Wenn Sie mit SQL noch nicht vertraut sind, lesen Sie unbedingt diese wichtigen SQL-Befehle , um die Verwendung von SQL zu verbessern.