PostgreSQL Datums- und Zeitoperationen

Einführung

In diesem Lab werden Sie die Datums- und Zeitoperationen von PostgreSQL erkunden, wobei der Schwerpunkt auf der Handhabung von Zeitzonen und der Durchführung von Berechnungen liegt. Sie beginnen mit dem Einfügen von Zeitstempeln mit Zeitzoneninformationen in eine Datenbanktabelle, um zu demonstrieren, wie Ereignisse gespeichert werden, die an verschiedenen geografischen Standorten stattfinden.

Das Lab führt Sie dann durch die Verwendung von Funktionen wie EXTRACT und INTERVAL, um Datums- und Zeitdaten zu bearbeiten und abzufragen. Sie lernen, wie Sie Daten über Zeitzonen hinweg abfragen und Zeitunterschiede zwischen Ereignissen berechnen können, wodurch Sie praktische Fähigkeiten für die Verwaltung von zeitlichen Daten in PostgreSQL erwerben.

Skills Graph

Verbindung zu PostgreSQL herstellen und die Tabelle events erstellen

In diesem Schritt stellen Sie eine Verbindung zur PostgreSQL-Datenbank her und erstellen die Tabelle events. Diese Tabelle speichert Ereignisnamen und die entsprechenden Zeitstempel mit Zeitzoneninformationen.

Öffnen Sie zunächst Ihr Terminal. Um sich als Benutzer postgres mit der PostgreSQL-Datenbank zu verbinden, führen Sie den folgenden Befehl aus:

sudo -u postgres psql

Dieser Befehl öffnet das interaktive PostgreSQL-Terminal, auch bekannt als die psql-Shell. Sie verwenden diese Shell, um SQL-Befehle auszuführen.

Lassen Sie uns nun die Tabelle events erstellen. Führen Sie den folgenden SQL-Befehl aus:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ
);

Dieser Befehl erstellt eine Tabelle namens events mit drei Spalten:

• id: Ein serieller Primärschlüssel (primary key), der für jedes neue Ereignis automatisch inkrementiert wird.
• event_name: Eine Zeichenkette (string), die den Namen des Ereignisses enthält.
• event_time: Ein Zeitstempel mit Zeitzoneninformationen (TIMESTAMPTZ). Dieser Datentyp ist entscheidend für die Handhabung von Ereignissen, die in verschiedenen Zeitzonen stattfinden.

Um die Tabellenerstellung zu überprüfen, können Sie die Tabellenstruktur mit dem folgenden Befehl beschreiben:

\d events

Dadurch wird das Tabellenschema angezeigt und die erfolgreiche Erstellung der Tabelle events bestätigt.

Zeitstempel mit Zeitzonen einfügen

In diesem Schritt fügen Sie Daten in die Tabelle events ein und geben für jedes Ereignis die Zeitzone an. Dies demonstriert, wie Ereignisse gespeichert werden, die an verschiedenen geografischen Standorten stattfinden.

Fügen Sie mit der psql-Shell (die im vorherigen Schritt verbunden wurde) die folgenden Daten in die Tabelle events ein:

INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00-08 America/Los_Angeles');

INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00+00 Europe/London');

INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00-08 America/Los_Angeles');

In diesen INSERT-Anweisungen:

• '2024-01-20 10:00:00-08 America/Los_Angeles' gibt einen Zeitstempel vom 20. Januar 2024 um 10:00 Uhr in der Zeitzone America/Los_Angeles (UTC-8) an.
• '2024-01-20 18:00:00+00 Europe/London' gibt einen Zeitstempel vom 20. Januar 2024 um 18:00 Uhr in der Zeitzone Europe/London (UTC+0) an.

Um die Dateneinfügung zu überprüfen, fragen Sie die Tabelle events ab:

SELECT * FROM events;

Sie sollten eine ähnliche Ausgabe wie diese sehen:

 id |   event_name   |         event_time
----+----------------+----------------------------
  1 | Conference Call | 2024-01-20 18:00:00+00
  2 | Project Meeting | 2024-01-20 18:00:00+00
  3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)

Beachten Sie, dass PostgreSQL die Zeitstempel in UTC (Coordinated Universal Time, koordinierte Weltzeit) speichert. Wenn Sie einen Zeitstempel mit einer Zeitzone einfügen, konvertiert PostgreSQL ihn in UTC und speichert ihn intern.

Verwendung von EXTRACT und AT TIME ZONE

In diesem Schritt lernen Sie, wie Sie die Funktion EXTRACT verwenden, um bestimmte Teile eines Zeitstempels (wie Jahr, Monat, Tag) zu extrahieren, und die Klausel AT TIME ZONE, um Zeitstempel in verschiedene Zeitzonen zu konvertieren.

Extrahieren wir zunächst das Jahr aus der Spalte event_time. Führen Sie die folgende Abfrage aus:

SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;

Diese Abfrage verwendet die Funktion EXTRACT, um das Jahr aus der Spalte event_time zu extrahieren und es als event_year zu aliasieren. Die Ausgabe wird sein:

   event_name   | event_year
----------------+------------
 Conference Call |       2024
 Project Meeting |       2024
 Follow-up Meeting |       2024
(3 rows)

Als Nächstes sehen wir uns an, wie Sie event_time mit der Klausel AT TIME ZONE in eine bestimmte Zeitzone konvertieren können. Um event_time in der Zeitzone America/Los_Angeles anzuzeigen, verwenden Sie die folgende Abfrage:

SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;

Diese Abfrage konvertiert event_time in die Zeitzone America/Los_Angeles und zeigt sie als los_angeles_time an. Die Ausgabe wird sein:

   event_name   |  los_angeles_time
----------------+---------------------
 Conference Call | 2024-01-20 10:00:00
 Project Meeting | 2024-01-20 10:00:00
 Follow-up Meeting | 2024-01-21 04:00:00
(3 rows)

Sie können EXTRACT und AT TIME ZONE kombinieren, um bestimmte Komponenten des Zeitstempels in einer bestimmten Zeitzone zu extrahieren. Um beispielsweise die Stunde der Ereignisse in der Zeitzone America/Los_Angeles zu ermitteln, verwenden Sie die folgende Abfrage:

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'America/Los_Angeles') AS event_hour_la FROM events;

Dies gibt die Stunde jedes Ereignisses in der Zeitzone America/Los_Angeles aus.

Zeitliche Differenzen mit INTERVAL berechnen

In diesem Schritt lernen Sie, wie Sie die Zeitdifferenz zwischen zwei Ereignissen mithilfe des Datentyps INTERVAL berechnen.

Um die Zeitdifferenz zu berechnen, können Sie einen Zeitstempel von einem anderen subtrahieren. Das Ergebnis ist ein INTERVAL-Wert, der die Differenz darstellt.

Berechnen wir die Zeitdifferenz zwischen dem 'Follow-up Meeting' und dem 'Conference Call'. Dazu können Sie eine Unterabfrage verwenden oder die Tabelle mit sich selbst verknüpfen (join). Hier ist ein Beispiel mit einem Join:

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    e2.event_time - e1.event_time AS time_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Diese Abfrage verknüpft die Tabelle events mit sich selbst und gibt den Tabellen die Aliasnamen e1 und e2. Anschließend werden die event_name aus beiden Tabellen ausgewählt und die Zeitdifferenz zwischen der event_time des 'Follow-up Meeting' und des 'Conference Call' berechnet. Die Ausgabe wird sein:

    event1     |     event2      |   time_difference
----------------+-----------------+---------------------
 Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)

Die Spalte time_difference zeigt das Intervall zwischen den beiden Ereignissen an.

Sie können auch bestimmte Komponenten aus dem INTERVAL-Wert mit der Funktion EXTRACT extrahieren. Um beispielsweise die Anzahl der Tage aus der Zeitdifferenz zu extrahieren, können Sie die folgende Abfrage verwenden:

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Diese Abfrage extrahiert die Anzahl der Tage aus der time_difference und zeigt sie als days_difference an.

Beenden Sie abschließend die PostgreSQL-Shell:

\q

Dadurch kehren Sie zum Terminal zurück.

Zusammenfassung

In diesem Lab haben Sie die Datums- und Zeitoperationen von PostgreSQL erkundet. Sie haben gelernt, wie man:

• Eine Tabelle mit einer TIMESTAMPTZ-Spalte erstellt, um Zeitstempel mit Zeitzoneninformationen zu speichern.
• Daten in die Tabelle einfügt und dabei die Zeitzone für jedes Ereignis angibt.
• Die Funktion EXTRACT verwendet, um bestimmte Teile eines Zeitstempels zu extrahieren, z. B. das Jahr, den Monat oder den Tag.
• Die Klausel AT TIME ZONE verwendet, um Zeitstempel in verschiedene Zeitzonen zu konvertieren.
• Die Zeitdifferenz zwischen zwei Zeitstempeln mithilfe des Datentyps INTERVAL berechnet.

Diese Fähigkeiten sind unerlässlich für die Verwaltung von zeitlichen Daten in PostgreSQL, insbesondere wenn es um Ereignisse geht, die an verschiedenen geografischen Standorten stattfinden.