SQL-Datums- und Zeitfunktionen in 5 gängigen SQL-Dialekten

Sind Sie verwirrt von den vielen Datums- und Zeitfunktionen, die in den verschiedenen SQL-Dialekten verwendet werden? In diesem Artikel fasse ich die Datums- und Zeitdatentypen zusammen, die in PostgreSQL, Oracle, SQLite, MySQL und T-SQL verwendet werden. Ich gebe auch Beispiele für die wichtigsten SQL-Datums- und Zeitfunktionen, die in diesen Dialekten verwendet werden. Es ist an der Zeit, Datums- und Zeitgurus zu werden!

Möchten Sie berechnen, wie oft Ihre Mitarbeiter zu spät zur Arbeit kommen? Oder wie lange es dauert, einen Auftrag abzuschließen? Es gibt viele Fälle, in denen Datenanalysten Berechnungen mit Datums- und Zeitwerten in SQL durchführen müssen. Die Datentypen und Funktionen für Datum und Uhrzeit unterscheiden sich jedoch in den verschiedenen SQL-Dialekten erheblich. Die Abfragen sehen sehr unterschiedlich aus, je nachdem, ob Sie sie z. B. in PostgreSQL oder SQL Server schreiben.

In diesem Artikel behandle ich die Grundlagen des Umgangs mit Datums- und Zeitangaben in verschiedenen SQL-Dialekten. Ich gebe auch Beispiele für wichtige Datums- und Zeitfunktionen.

Wenn Sie zu einem bestimmten Teil springen möchten, finden Sie hier die SQL-Dialekte, die in diesem Artikel behandelt werden:

• PostgreSQL
• Oracle
• SQLite
• MySQL
• T-SQL (MS SQL Server)

Mit dem Kurs " Datentypen in SQL " können Sie noch heute mit dem Erlernen von Datentypen für Datum und Uhrzeit beginnen. Dieser interaktive Kurs behandelt Datentypen, die in allen gängigen relationalen Datenbankmanagementsystemen funktionieren, einschließlich SQL Server, MySQL, Oracle und PostgreSQL.

SQL-Funktionen für Datum und Uhrzeit in verschiedenen Dialekten

Es ist immer einfacher, neue Informationen durch reale Anwendungsfälle zu erhalten. Um zu verstehen, wie Datums- und Zeitangaben in verschiedenen SQL-Dialekten verarbeitet werden können, schlage ich vor, die folgende Tabelle mit den für 2022 geplanten Reisen zu verwenden. Hier haben wir sowohl Datums- als auch Zeitstempel mit einem Zeitzonen-Offset.

Schauen wir uns nun an, wie wir diese Daten in verschiedenen SQL-Dialekten verarbeiten können.

PostgreSQL-Funktionen für Datum und Uhrzeit

Es gibt vier Datentypen, die Datums- und Zeitangaben in PostgreSQL verarbeiten:

• Der date Datentyp wird verwendet, um Datumsangaben ohne genaue Zeitangabe zu speichern (z. B. '2022-21-03').
• Der Datentyp time Datentyp ermöglicht es, die Zeit ohne Datum zu speichern (z. B. '8:34:59'). Standardmäßig enthält der Datentyp time keine Informationen über die Zeitzone. Wenn Sie die Zeitzone angeben müssen, müssen Sie use time with time zone verwenden. Es wird jedoch empfohlen, den nächsten Datentyp zu verwenden, wenn Sie mit Zeitzonen arbeiten.
• Der timestamp Datentyp ist in der Praxis sehr nützlich, da er es uns ermöglicht, den vollständigen Zeitstempel zu speichern - die Daten mit der genauen Uhrzeit (z. B. '2022-07-30 06:55:34'). Die Genauigkeit kann bis zu 1 Mikrosekunde betragen. Wie beim Datentyp time müssen Sie timestamp mit Zeitzone verwenden, wenn Sie Informationen über die Zeitzone einschließen möchten.
• Der Datentyp interval wird verwendet, um Informationen über Zeitintervalle (d. h. Dauer) zu speichern. Sie können die Menge der gespeicherten Felder einschränken, indem Sie eine entsprechende Phrase hinzufügen (z. B. YEAR, DAY, YEAR TO MONTH, HOUR TO SECOND); weitere Einzelheiten finden Sie in der Dokumentation von PostgreSQL. Diese Intervalle können zu den oben definierten Datentypen addiert oder subtrahiert werden.

Sie können mehr über diese und andere PostgreSQL-Datentypen in diesem Artikel lesen; lassen Sie uns zu einem praktischen Anwendungsfall übergehen.

Zum Erstellen unserer trips Tabelle in PostgreSQL zu erstellen, verwenden wir den folgenden Code:

Beachten Sie, dass wir den Datentyp date verwenden, um Informationen über das Abfahrts- und Rückgabedatum zu speichern. Wir verwenden auch timestamp mit Zeitzone, um Abfahrts- und Ankunftszeiten zu speichern. Mit diesem Datentyp können wir Informationen zu Datum, Uhrzeit und Zeitzone speichern; dies ist wichtig, da alle unsere Reisen mehrere Zeitzonen durchqueren.

Um die PostgreSQL Datums- und Zeitfunktionen zu üben, nehmen wir an, dass wir herausfinden wollen:

1. Die Dauer unserer Reise in Tagen.
2. Die Dauer unseres Fluges von zu Hause in New York City zur Zielstadt.
3. Das Datum, an dem wir mit den Reisevorbereitungen beginnen müssen, was in etwa 14 Tagen der Fall sein dürfte.

Wir brauchen diese Informationen für jede Reise in unserer Tabelle. Hier sehen Sie, wie Sie sie mit PostgreSQL finden:

Beachten Sie, dass wir +1 hinzugefügt haben, um sowohl den Abfahrts- als auch den Ankunftstag zu unserer Reisedauer hinzuzufügen. Hier sind die Ergebnisse:

Es gibt noch viele andere Dinge, die Sie mit Datum und Zeit in PostgreSQL tun können. In der folgenden Tabelle habe ich zusammengefasst, wie das geht:

• Das aktuelle Datum und die aktuelle Zeit abfragen.
• Daten/Zeiten subtrahieren.
• Intervalle addieren/subtrahieren.
• Bestimmte Teile des Datums/der Uhrzeit extrahieren.
• Erzeugen eines Datums/einer Uhrzeit aus seinen Teilen.
• Abschneiden von Daten.
• Konvertieren von Strings in Datums-/Zeitobjekte.
• Umwandlung von Datums-/Zeitobjekten in Strings mit bestimmter Formatierung.

Weitere Einzelheiten zu den oben genannten Funktionen finden Sie in der PostgreSQL-Dokumentation zu Datums-/Zeittypen, Datums-/Zeitfunktionen und -operatoren sowie Datums-/Zeitformatierungsfunktionen.

Oracle Datums- und Zeitfunktionen

Hier sind die Datentypen für die Verarbeitung von Datums- und Zeitangaben in der Oracle-Datenbank:

• DATE. Dieser Datentyp speichert Datums- und Zeitinformationen, einschließlich Jahrhundert, Jahr, Monat, Datum, Stunde, Minute und Sekunde. Wenn keine Zeitkomponente angegeben wird, ist die Standardzeit Mitternacht.
• Der TIMESTAMP Datentyp ist eine Erweiterung des DATE Datentyps, da er auch Bruchteile von Sekunden speichert.
  • TIMESTAMP WITH TIME ZONE ist eine Variante des Datentyps TIMESTAMP. Er speichert einen Zeitzonen-Offset oder den Namen einer Zeitzonenregion.
  • TIMESTAMP WITH LOCAL TIME ZONE ist eine weitere Variante von TIMESTAMP. Anstatt einen Zeitzonen-Offset als Teil der Spaltendaten zu speichern, werden die Zeitzoneninformationen einfach auf die Zeitzone der Datenbank normalisiert, d.h. Oracle gibt sie in der lokalen Sitzungszeitzone des Benutzers aus.
• Der Datentyp INTERVAL YEAR TO MONTH Datentyp speichert eine Zeitspanne unter Verwendung des YEAR und MONTH
• Der Datentyp INTERVAL DAY TO SECOND speichert eine Zeitspanne in Form von Tagen, Stunden, Minuten und Sekunden.

Mehr über die Datentypen von Oracle datetime können Sie in der Oracle-Dokumentation nachlesen; wir kommen nun zu unserem praktischen Anwendungsfall. Erstellen wir nun die trips Tabelle in der Oracle-Datenbank.

Wie Sie sehen, verwenden wir den Datentyp DATE für unser Abfahrts- und Rückreisedatum. (Wir brauchen hier keine Zeitzoneninformationen oder eine Genauigkeit bis auf Sekundenbruchteile). Für unsere Abfahrts- und Ankunftszeiten verwenden wir TIME WITH TIME ZONE, da wir Daten über verschiedene Zeitzonen hinweg haben.

Beachten Sie, dass wir beim Einfügen von Werten den Datentyp für jeden Wert angeben. Alternativ können wir auch das Standardformat von Oracle verwenden, ohne den Datentyp anzugeben (z. B. '21-MAR-2022' und '21-MAR-2022 8.00.00 AM -04.00'). Sie können die Standardformate mit Hilfe der Initialisierungsparameter NLS_DATE_FORMAT, NLS_DATE_LANGUAGE und NLS_TIMESTAMP_TZ_FORMAT ändern. Weitere Informationen finden Sie in der Dokumentation von Oracle.

Für jedes Reiseziel wollen wir wiederum die Dauer der Reise in Tagen (einschließlich Abflug- und Ankunftstag), die Dauer des Fluges in die Zielstadt und das Datum, an dem wir mit den Reisevorbereitungen beginnen müssen, herausfinden, wobei wir davon ausgehen, dass wir 14 Tage im Voraus beginnen wollen.

Hier sehen Sie, wie Sie diese Informationen in Oracle finden:

Es gibt noch viele weitere Möglichkeiten, mit Daten und Zeiten in Oracle zu arbeiten. Die folgende Tabelle fasst zusammen, wie das geht:

• Abrufen des aktuellen Datums und der aktuellen Uhrzeit.
• Daten und Zeitensubtrahieren.
• Intervalle addieren/subtrahieren.
• Bestimmte Teile von Datum/Uhrzeit extrahieren.
• Abschneiden von Daten.
• Konvertieren von Strings in Datums-/Zeitobjekte.
• Umwandlung von Datums-/Zeitobjekten in Strings mit bestimmter Formatierung.

Weitere Einzelheiten zu jeder der oben genannten Funktionen - und zu einigen anderen nützlichen Datums- und Zeitfunktionen in Oracle - finden Sie in diesem Artikel und in der Oracle-Dokumentation.

SQLite Datums- und Zeitfunktionen

SQLite hat keine speziellen Datentypen für die Speicherung von Datums- und Zeitangaben. Mit den SQLite-Datums- und Zeitfunktionen können Sie jedoch Datums- und Zeitangaben als TEXT, REAL oder INTEGER Werte speichern:

• TEXT als ISO 8601-Strings ('YYYY-MM-DD HH:MM:SS.SSS').
• REAL als die Anzahl der Tage seit Mittag in Greenwich am 24. November 4714 v. Chr.
• INTEGER als die Anzahl der Sekunden seit 1970-01-01 00:00:00 UTC.

Hier sind die SQLite Datums- und Zeitfunktionen:

• Die Funktion date() Funktion gibt das Datum im Format JJJJ-MM-TT zurück.
• Die Funktion time() Funktion gibt die Uhrzeit im Format HH:MM:SS zurück.
• Die Funktion datetime() Funktion gibt den Zeitstempel im Format JJJJ-MM-TT HH:MM:SS zurück.
• Die Funktion julianday() gibt den Julianischen Tag zurück, d. h. die Anzahl der Tage seit Mittag in Greenwich, England, am 24. November 4714 v. Chr.
• Die Funktion strftime() gibt das Datum zurück, das entsprechend der als erstes Argument angegebenen Formatzeichenfolge formatiert wurde.

Um unsere Reisetabelle in SQLite zu erstellen, können wir den folgenden Code verwenden:

Beachten Sie, dass wir den Datentyp TEXT verwenden, um alle unsere Datums- und Zeitangaben zu speichern. Dann fügen wir die Werte ein, indem wir Datum und Uhrzeit im üblichen Format angeben, wenn nötig sogar mit Zeitzonen.

Jetzt wollen wir die Datums- und Zeitfunktionen von SQLite üben, indem wir unsere Reisedauer in Tagen (einschließlich Abreise- und Ankunftstag), die Flugdauer in Stunden und das Datum, an dem wir mit den Reisevorbereitungen beginnen müssen, berechnen.

Hier sehen Sie, wie Sie diese Daten in SQLite berechnen können:

In dieser Abfrage haben wir die Funktion julianday() verwendet, um die Differenz zwischen zwei Daten/Zeitstempeln zu berechnen. Diese Funktion gibt die Anzahl der Tage zurück, was wir für die Reisedauer erwarten. Die Flugdauer wird besser in Stunden angegeben - wir multiplizieren das Ergebnis also mit 24, um die Flugdauer in Stunden zu erhalten. Beachten Sie auch, wie elegant die Funktion date() das Addieren/Subtrahieren von Tagen von einem Datumswert ermöglicht. Auf ähnliche Weise können Sie auch Jahre, Monate, Stunden, Minuten und Sekunden addieren bzw. subtrahieren.

Schauen wir uns an, was wir sonst noch mit Datums- und Zeitangaben in SQLite machen können. In der folgenden Tabelle habe ich zusammengefasst, wie das geht:

• Ermittelt das aktuelle Datum und die aktuelle Uhrzeit.
• Daten subtrahieren.
• Zeiten subtrahieren.
• Intervalle addieren/subtrahieren.
• Bestimmte Teile von Datum/Uhrzeit extrahieren.
• Abschneiden von Daten.
• Konvertieren von Datums-/Zeitobjekten in Strings mit spezifischer Formatierung.

Im Gegensatz zu den Tabellen, die wir für andere SQL-Dialekte haben, entsprechen die Spalten hier nicht bestimmten Datentypen. Sie enthalten lediglich Beispiele für die Verarbeitung von (1) Datumsangaben, (2) Zeitangaben und Datums- und Zeitangaben zusammen (d.h. Zeitstempel).

Weitere Einzelheiten zu den SQLite-Datums- und Zeitfunktionen finden Sie in der SQLite-Dokumentation.

MySQL Datums- und Zeitfunktionen

Es gibt fünf Datentypen zur Verarbeitung von Datums- und Zeitangaben in MySQL:

• Der DATE Datentyp wird für Werte mit einem Datumsteil, aber ohne Zeitteil verwendet.
• Der DATETIME Datentyp wird für Werte verwendet, die sowohl einen Datums- als auch einen Zeitteil enthalten. Sie können auch Sekundenbruchteile haben, wenn Sie die Werte von DATETIME in die Tabelle einfügen. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• Der Datentyp TIMESTAMP Datentyp wird ebenfalls für Werte verwendet, die sowohl Datums- als auch Zeitangaben enthalten. Er unterstützt auch Bruchteile von Sekunden. Außerdem bietet dieser Datentyp ab MySQL 8.0.19 volle Unterstützung für Zeitzoneninformationen. Der unterstützte Bereich ist '1970-01-01 00:00:01' UTC bis '2038-01-19 03:14:07' UTC.
• Der Datentyp TIME Datentyp wird verwendet, um eine Tageszeit und auch die verstrichene Zeit oder ein Zeitintervall zwischen zwei Ereignissen darzustellen. TIME Werte können von '-838:59:59' bis '838:59:59' reichen. Bruchteile von Sekunden werden unterstützt.
• Der Datentyp YEAR Datentyp wird zur Darstellung von Jahreswerten verwendet. Der unterstützte Bereich ist 1901 bis 2155. Beachten Sie auch, dass YEAR Werte in einer Vielzahl von Formaten akzeptiert, z. B. '2021', 2021, '21', 21.

Mehr über die Datums- und Zeitdatentypen in MySQL erfahren Sie hier.

Wiederholen wir nun unser Beispiel mit den Reisen, aber dieses Mal in MySQL. Wir beginnen mit der Erstellung der trips Tabelle:

Wie Sie sehen können, verwenden wir den Datentyp DATE, um Abfahrts- und Rückreisedaten zu speichern. Für die Abfahrts- und Ankunftszeiten haben wir den Datentyp TIMESTAMP gewählt, da wir die Informationen über die Zeitzone erhalten wollen.

Zur Berechnung der Reisedauer (einschließlich Abfahrts- und Ankunftstag), der Flugdauer und des Datums für den Beginn der Vorbereitung (14 Tage im Voraus) können wir die folgende MySQL-Abfrage verwenden:

Wie Sie an dieser Abfrage sehen können, gibt es einige sehr nützliche MySQL-Funktionen, die Daten und Zeiten verarbeiten. In der Tat gibt es viele solcher Funktionen. Um einen Eindruck davon zu bekommen, was Sie mit Datums- und Zeitangaben in MySQL alles machen können, sehen Sie sich die folgende Tabelle an und lernen Sie, wie das geht:

• Ermitteln des aktuellen Datums und der aktuellen Uhrzeit.
• Datums- und Zeitangaben subtrahieren.
• Intervalle addieren/subtrahieren.
• Bestimmte Teile des Datums/der Uhrzeit extrahieren.
• Erzeugen eines Datums/einer Uhrzeit aus seinen Bestandteilen.
• Konvertieren von Zeichenketten in Datum/Uhrzeit-Objekte.
• Ändern der Formatierung von Datums-/Zeitobjekten.

Weitere Einzelheiten zu jeder der oben genannten Funktionen finden Sie in der MySQL-Dokumentation zu Datums- und Zeitfunktionen.

T-SQL-Funktionen für Datum und Uhrzeit

Abschließend wollen wir uns mit den Datums- und Zeitfunktionen beschäftigen, die Sie kennen sollten, wenn Sie mit MS SQL Server arbeiten und Transact-SQL (T-SQL) verwenden.

Zunächst einmal geht es um die Datums- und Zeitdatentypen, die von T-SQL unterstützt werden:

• Der Datentyp time Datentyp wird zur Speicherung von Zeitwerten, einschließlich Sekundenbruchteilen, verwendet.
• Der Datentyp date Datentyp wird verwendet, um Datumswerte ohne Zeitanteile zu speichern.
• Der Datentyp smalldatetime wird in T-SQL verwendet, um Datums- und Zeitangaben im Bereich von 1900-01-01 bis 2079-06-06 mit einer Genauigkeit von bis zu 1 Minute zu speichern.
• Der Datentyp datetime Datentyp kann Datums- und Zeitangaben im Bereich von 1753-01-01 bis 9999-12-31 mit einer Genauigkeit von bis zu 0,00333 Sekunden speichern.
• T-SQL hat auch den datetime2 Datentyp. Er speichert Werte in einem noch größeren Bereich (0001-01-01 00:00:00.0000000 bis 9999-12-31 23:59:59.9999999) und definiert Sekundenbruchteile bis zu 100 Nanosekunden.
• Schließlich speichert der datetimeoffset Datentyp schließlich speichert Datums- und Zeitangaben mit dem Offset der Zeitzone. Er hat denselben Bereich und dieselbe Genauigkeit wie der Datentyp datetime2.

Mehr über Datums- und Zeitdatentypen in Transact-SQL können Sie hier nachlesen.

Und nun wiederholen wir unser Beispiel mit den Reisen mit T-SQL. Wie üblich beginnen wir mit der Erstellung der trips Tabelle:

Beachten Sie, dass wir (1) den Datentyp Datum verwenden, um das Abfahrts- und Rückreisedatum zu speichern, und (2) den Datentyp datetimeoffset, um die Abfahrts- und Ankunftszeit zu speichern (um die Zeitzoneninformationen zu erhalten).

Berechnen wir nun die Reisedauer (einschließlich Abflug- und Ankunftstag), die Flugdauer und das Datum für den Beginn der Vorbereitungen (14 Tage im Voraus) mit T-SQL:

Wie Sie an diesem Beispiel sehen können, verfügt T-SQL über die Funktion DATEDIFF(), die nicht nur Datumsangaben, sondern auch Zeit- und Datetime-Werte verarbeitet. Bei der Berechnung der Flugdauer konnten wir jedoch nur die ganze Zahl der Stunden erhalten, anstatt das genaue Intervall mit Minuten. Wenn Sie genauere Informationen benötigen, können Sie für diese Funktion immer einen anderen Datumsteil auswählen (z. B. Minute, um die Flugdauer in Minuten zu erhalten). Lesen Sie diesen Artikel, um zu sehen, wie Sie die Ausgabe dieser Funktion verarbeiten können, um das Intervall im gewünschten Format zu erhalten.

Die folgende Tabelle fasst einige andere Operationen zusammen, die Sie mit Datums- und Zeitangaben in T-SQL durchführen können:

• Ermitteln des aktuellen Datums und der aktuellen Uhrzeit.
• Subtrahieren von Datums- und Zeit angaben.
• Intervalle addieren/subtrahieren.
• Bestimmte Teile des Datums/Zeitpunkts extrahieren (z.B. Monat, Minute).
• Erzeugen von Datum/Uhrzeit aus ihren Teilen.
• Konvertieren von Zeichenketten in Datum/Zeit-Objekte.
• Ändern der Datums- und Zeitformate.

Wie Sie an diesem Beispiel sehen können, verfügt T-SQL über die Funktion DATEDIFF(), die nicht nur Datums-, sondern auch Zeit- und Datetime-Werte verarbeitet. Bei der Berechnung der Flugdauer konnten wir jedoch nur die ganze Zahl der Stunden erhalten, anstatt das genaue Intervall mit Minuten. Wenn Sie genauere Informationen benötigen, können Sie für diese Funktion immer einen anderen Datumsteil auswählen (z. B. Minute, um die Flugdauer in Minuten zu erhalten). Lesen Sie diesen Artikel, um zu sehen, wie Sie die Ausgabe dieser Funktion verarbeiten können, um das Intervall im gewünschten Format zu erhalten.

Die folgende Tabelle fasst einige andere Operationen zusammen, die Sie mit Datums- und Zeitangaben in T-SQL durchführen können:

• Ermitteln des aktuellen Datums und der aktuellen Uhrzeit.
• Subtrahieren von Datums- und Zeit angaben.
• Intervalle addieren/subtrahieren.
• Bestimmte Teile des Datums/Zeitpunkts extrahieren (z.B. Monat, Minute).
• Erzeugen von Datum/Uhrzeit aus ihren Teilen.
• Konvertieren von Zeichenketten in Datum/Zeit-Objekte.
• Ändern der Datums- und Zeitformate.

Weitere Einzelheiten zu den Datums- und Zeitfunktionen von SQL Server finden Sie in der T-SQL-Dokumentation.

Üben wir die SQL Datums- und Zeitfunktionen!

Ich hoffe, dieser Artikel hat Ihnen ein allgemeines Verständnis dafür vermittelt, wie Datums- und Zeitangaben in verschiedenen SQL-Dialekten verarbeitet werden können. Jetzt ist es an der Zeit, zu üben!

Ich empfehle Ihnen, mit einem interaktiven Kurs zu beginnen. Sie können sich umfassendes Wissen über SQL-Daten- und Zeitfunktionen aneignen, diese Funktionen anhand von Beispielen aus der Praxis üben und eine Anleitung erhalten, wenn Sie nicht weiterkommen:

• Standard-SQL-Funktionen enthält 211 Codieraufgaben. Sie üben die Standard-SQL-Funktionen, die bei der Verarbeitung von Textdaten, numerischen Daten, Datums- und Zeitangaben und vielem mehr verwendet werden.
• Data Types in SQL enthält 89 interaktive Übungen. Es führt Sie in die gängigen Datentypen in SQL Server, MySQL, Oracle und PostgreSQL ein.

Wenn Sie sich ohne Programmierkenntnisse ein umfassendes Wissen über SQL aneignen wollen, empfehle ich Ihnen den SQL von A bis Z Lernpfad. Er umfasst 7 interaktive Kurse, die das Schreiben einfacher Abfragen, das Kombinieren von Daten aus mehreren Tabellen, das Schreiben komplexer SQL-Abfragen mit Unterabfragen und die Verwendung gängiger Tabellenausdrücke, Fensterfunktionen und vieles mehr behandeln.

Vielen Dank für die Lektüre und viel Spaß beim Lernen.