PostgreSQL Datums- und Zeitoperationen

Einführung

In diesem Lab werden Sie die leistungsstarken Datums- und Zeitoperationen von PostgreSQL erkunden. Sie lernen, wie Sie mit Zeitstempeln arbeiten, die Zeitzoneninformationen enthalten, eine entscheidende Fähigkeit für Anwendungen, die Daten aus verschiedenen geografischen Standorten verarbeiten.

Sie beginnen mit der Erstellung einer Tabelle zur Speicherung von Event-Daten, wobei der Datentyp TIMESTAMPTZ verwendet wird, um die Zeitzonenkenntnis sicherzustellen. Anschließend fügen Sie Daten für Events ein, die in verschiedenen Zeitzonen stattfinden, und lernen, wie PostgreSQL diese Daten normalisiert. Abschließend üben Sie das Abfragen und Manipulieren dieser temporären Daten mit Funktionen wie EXTRACT, der Klausel AT TIME ZONE und der Berechnung von Zeitdifferenzen mit INTERVAL.

Tabelle für zeitbasierte Ereignisse erstellen

In diesem Schritt verbinden Sie sich mit der PostgreSQL-Datenbank und erstellen eine Tabelle zur Speicherung von Event-Informationen. Diese Tabelle verwendet den Datentyp TIMESTAMPTZ, der für die genaue Speicherung von Zeitstempeln aus verschiedenen Zeitzonen unerlässlich ist.

Verständnis von TIMESTAMPTZ

Der Datentyp TIMESTAMPTZ (timestamp with time zone) in PostgreSQL ist dafür konzipiert, Daten und Zeiten aus der ganzen Welt zu verarbeiten. Wenn Sie einen Wert mit einer bestimmten Zeitzone einfügen, konvertiert PostgreSQL ihn zur Speicherung in die koordinierte Weltzeit (UTC). Diese Standardisierung erleichtert den Vergleich und die Berechnung von Zeiten unabhängig von ihrer ursprünglichen Zeitzone.

Schritt 1: Verbindung zu PostgreSQL herstellen

Öffnen Sie zunächst ein Terminal. Verbinden Sie sich mit der PostgreSQL-Datenbank über die interaktive Shell psql. Alle nachfolgenden SQL-Befehle werden Sie in dieser Shell ausführen.

sudo -u postgres psql

Sie sehen nun die PostgreSQL-Eingabeaufforderung, die wie folgt aussieht: postgres=#.

Schritt 2: Erstellen der Tabelle events

Führen Sie nun den folgenden SQL-Befehl aus, um die Tabelle events zu erstellen. Diese Tabelle speichert eine ID, einen Namen und eine Zeit für jedes Event.

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 automatisch inkrementierender Primärschlüssel.
• event_name: Ein Textfeld für den Namen des Events.
• event_time: Ein Zeitstempel mit Zeitzonenunterstützung.

Schritt 3: Überprüfung der Tabellenerstellung

Um zu bestätigen, dass die Tabelle korrekt erstellt wurde, können Sie ihre Struktur mit dem Befehl \d überprüfen.

\d events

Sie sollten das Schema der Tabelle events sehen, das ihre Spalten und Datentypen bestätigt.

                                     Table "public.events"
   Column   |          Type          | Collation | Nullable |              Default
------------+------------------------+-----------+----------+-----------------------------------
 id         | integer                |           | not null | nextval('events_id_seq'::regclass)
 event_name | character varying(255) |           |          |
 event_time | timestamp with time zone |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)

Sie haben die Tabelle erfolgreich für die Speicherung zeitbasierter Daten eingerichtet.

Daten mit Zeitzoneninformationen einfügen

In diesem Schritt fügen Sie Datensätze in die Tabelle events ein. Jeder Datensatz wird einen Zeitstempel haben, der mit einer bestimmten Zeitzone verknüpft ist, was zeigt, wie PostgreSQL Daten aus verschiedenen geografischen Standorten verarbeitet.

Schritt 1: Einfügen von Event-Daten

Führen Sie in der psql-Shell die folgenden INSERT-Anweisungen aus, um drei Events zu Ihrer Tabelle hinzuzufügen. Beachten Sie, wie jeder event_time einen Zeitzonenbezeichner enthält.

Fügen Sie einen Konferenzanruf aus Los Angeles (UTC-8) ein:

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

Fügen Sie ein Projektmeeting aus London (UTC+0) ein:

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

Fügen Sie ein Follow-up-Meeting, ebenfalls aus Los Angeles, ein:

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

Schritt 2: Überprüfung des Dateneinfügens

Um zu sehen, wie PostgreSQL diese Daten gespeichert hat, fragen Sie die Tabelle events ab.

SELECT * FROM events;

Sie sollten die folgende Ausgabe 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 alle event_time-Werte in UTC angezeigt werden (angezeigt durch den Offset +00). Zum Beispiel wurde 10:00:00 in Los Angeles (UTC-8) in 18:00:00 UTC umgewandelt. Diese interne Umwandlung ermöglicht konsistente zeitbasierte Vergleiche.

Zeitstempel abfragen und formatieren

Nachdem Sie Daten in UTC gespeichert haben, können Sie diese abfragen und in jeder gewünschten Zeitzone anzeigen. In diesem Schritt verwenden Sie die Funktion EXTRACT und die Klausel AT TIME ZONE, um Ihre Zeitstempel zu manipulieren und zu formatieren.

Schritt 1: Extrahieren von Teilen eines Zeitstempels mit EXTRACT

Die Funktion EXTRACT ermöglicht es Ihnen, bestimmte Komponenten (wie Jahr, Monat, Stunde) aus einem Zeitstempel zu extrahieren. Holen wir uns das Jahr für jedes Event.

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

Die Abfrage extrahiert das Jahr aus dem gespeicherten UTC-Zeitstempel.

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

Schritt 2: Zeitstempel mit AT TIME ZONE konvertieren

Um die gespeicherte UTC-Zeit in einer bestimmten lokalen Zeitzone anzuzeigen, verwenden Sie die Klausel AT TIME ZONE. Konvertieren wir alle Event-Zeiten zurück in die Zeitzone America/Los_Angeles.

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

Diese Abfrage nimmt die UTC event_time und zeigt ihre entsprechende lokale Zeit in Los Angeles an.

    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 12:00:00
(3 rows)

Wie Sie sehen können, wird das 'Project Meeting', das um 18:00:00 UTC stattfand, korrekt als 10:00:00 in der Zeit von Los Angeles angezeigt.

Schritt 3: Kombinieren von EXTRACT und AT TIME ZONE

Sie können diese Funktionen für leistungsfähigere Abfragen kombinieren. Um beispielsweise die Stunde jedes Events anzuzeigen, wie sie in der Zeitzone Europe/London aufgetreten ist:

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;

Diese Abfrage konvertiert zuerst die Zeit in die Londoner Zeitzone und extrahiert dann die Stunde.

    event_name     | event_hour_london
-------------------+-------------------
 Conference Call   |                18
 Project Meeting   |                18
 Follow-up Meeting |                20
(3 rows)

Zeitdifferenzen berechnen

Eine häufige Anforderung ist die Berechnung der Dauer zwischen zwei Ereignissen. In PostgreSQL führt die Subtraktion eines Zeitstempels von einem anderen zu einem Datentyp INTERVAL, der eine Zeitdauer darstellt.

Schritt 1: Berechnung der Differenz zwischen zwei Events

Finden wir die Zeit, die zwischen dem 'Conference Call' und dem 'Follow-up Meeting' vergangen ist. Dies können wir erreichen, indem wir die Tabelle events mit sich selbst verknüpfen.

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 funktioniert wie folgt:

• Erstellung zweier virtueller Instanzen der Tabelle events, e1 und e2.
• Filterung von e1 auf nur die Zeile 'Conference Call'.
• Filterung von e2 auf nur die Zeile 'Follow-up Meeting'.
• Subtraktion der event_time von e1 von e2.

Das Ergebnis ist ein INTERVAL-Wert.

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

Die Zeitdifferenz beträgt 1 Tag und 2 Stunden.

Schritt 2: Extrahieren von Komponenten aus einem INTERVAL

Sie können auch EXTRACT auf einem INTERVAL verwenden, um bestimmte Teile der Dauer zu erhalten, wie z. B. die Anzahl der Tage oder Stunden. Extrahieren wir nur die Anzahl der Tage aus der vorherigen Berechnung.

SELECT
    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';

Dies gibt Ihnen einen numerischen Wert für die Tage.

 days_difference
-----------------
               1
(1 row)

Schritt 3: PostgreSQL beenden

Sie haben die Übungen für dieses Lab abgeschlossen. Sie können nun die psql-Shell verlassen und zum Hauptterminal zurückkehren.

\q

Zusammenfassung

In diesem Lab haben Sie wesentliche Datums- und Zeitoperationen in PostgreSQL kennengelernt. Sie haben gelernt, wie Sie:

• Eine Tabelle mit dem Datentyp TIMESTAMPTZ erstellen, um zeitzonenbewusste Informationen korrekt zu speichern.
• Zeitstempel mit angegebenen Zeitzonen einfügen und beobachten, wie PostgreSQL diese zur Speicherung in UTC konvertiert.
• Die Funktion EXTRACT verwenden, um spezifische Komponenten wie das Jahr oder die Stunde aus einem Zeitstempel abzurufen.
• Die Klausel AT TIME ZONE verwenden, um gespeicherte UTC-Zeiten in jeder gewünschten lokalen Zeitzone anzuzeigen.
• Die Dauer zwischen zwei Ereignissen berechnen, die zu einem INTERVAL führt, und Komponenten aus diesem Intervall extrahieren.

Diese Fähigkeiten sind grundlegend für die Entwicklung robuster Anwendungen, die zeitbezogene Daten über verschiedene Regionen hinweg genau verwalten.