0Pricing
SQL Academy · Lektion

Temporale und versionierte Zeilen

Abfragen zur Gültigkeitszeit und zum Stand zu einem bestimmten Zeitpunkt

Temporale und versionierte Zeilen ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was sind temporale Tabellen?

Mit temporalen Tabellen können Sie verfolgen, wie sich Daten im Laufe der Zeit verändern. Statt eine Zeile bei einer Änderung zu überschreiben, bewahrt eine temporale Tabelle jede Version dieser Zeile auf, jeweils mit dem Zeitraum, in dem sie gültig war.

Es gibt zwei zentrale Konzepte: Gültigkeitszeit (wann die Tatsache in der realen Welt zutraf) und Transaktionszeit (wann die Datenbank die Tatsache erfasst hat). Zusammen ergeben sie eine vollständig bitemporale Tabelle.

Gültigkeitszeit vs. Transaktionszeit

Gültigkeitszeit bezeichnet den Zeitraum, in dem eine Tatsache in der realen Welt zutrifft — beispielsweise das Gehalt eines Mitarbeiters vom 2020-01-01 bis zum 2022-06-30. Die Transaktionszeit bezeichnet den Zeitpunkt, zu dem die Datenbankzeile eingefügt oder abgelaufen ist. Zusammen beantworten sie zwei Fragen: Was war wahr? und Wann wussten wir davon?

Die meisten praktischen Anwendungsfälle beginnen mit der Erfassung der Gültigkeitszeit. Diese können Sie manuell mithilfe der Spalten valid_from und valid_to implementieren.

Eine Tabelle für die Gültigkeitszeit erstellen

Die einfachste Möglichkeit, versionierte Zeilen zu speichern, besteht darin, Zeitstempelspalten valid_from und valid_to hinzuzufügen. Ein valid_to-Wert von NULL (oder ein weit in der Zukunft liegender Platzhalter wie 9999-12-31) bedeutet, dass die Zeile derzeit aktiv ist.

CREATE TABLE employee_salary (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_from  DATE NOT NULL,
  valid_to    DATE
);

INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES
  (1, 50000, '2020-01-01', '2022-06-30'),
  (1, 60000, '2022-07-01', NULL);

Die aktuelle Version abfragen

Um die derzeit aktive Zeile für jeden Mitarbeiter zu finden, filtern Sie nach Zeilen, bei denen valid_to IS NULL gilt (offenes Ende), oder bei denen das heutige Datum innerhalb des Gültigkeitsbereichs liegt. Ein Platzhalter wie '9999-12-31' vereinfacht Bereichsvergleiche.

SELECT employee_id, salary
FROM employee_salary
WHERE valid_to IS NULL
ORDER BY employee_id;

Stichtagsabfragen

Eine Stichtagsabfrage fragt: Wie sahen die Daten zu einem bestimmten Zeitpunkt aus? Sie filtern nach Zeilen, bei denen der angegebene Zeitstempel innerhalb des Gültigkeitszeitraums liegt. Das ist eine der leistungsfähigsten Eigenschaften temporaler Tabellen.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_from, valid_to
FROM employee_salary
WHERE employee_id = 1
  AND valid_from <= '2021-03-15'
  AND (valid_to IS NULL OR valid_to > '2021-03-15');

Eine versionierte Zeile aktualisieren

Wenn sich eine Tatsache ändert, führen Sie kein UPDATE der bestehenden Zeile durch. Stattdessen schließen Sie die aktuelle Zeile, indem Sie valid_to setzen, und fügen eine neue Zeile mit dem neuen Wert ein. So bleibt die vollständige Historie erhalten.

-- Employee 1 gets a raise effective 2023-01-01
BEGIN;

-- Close the current open row
UPDATE employee_salary
SET valid_to = '2022-12-31'
WHERE employee_id = 1
  AND valid_to IS NULL;

-- Insert the new version
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES (1, 72000, '2023-01-01', NULL);

COMMIT;

daterange für Gültigkeitszeiträume verwenden

Der PostgreSQL-Typ daterange bildet einen Gültigkeitszeitraum elegant als einzelne Spalte ab. Mit dem Operator @> (enthält) können Sie prüfen, ob ein Datum innerhalb des Bereichs liegt, und mit einer Exclusion-Constraint überlappende Zeiträume für dieselbe Entität verhindern.

CREATE TABLE employee_salary_v2 (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_period DATERANGE NOT NULL,
  EXCLUDE USING GIST (employee_id WITH =, valid_period WITH &&)
);

INSERT INTO employee_salary_v2 (employee_id, salary, valid_period)
VALUES
  (1, 50000, '[2020-01-01, 2022-07-01)'),
  (1, 60000, '[2022-07-01, infinity)');

Stichtagsabfrage mit daterange

Mit dem Ansatz über daterange wird die Stichtagsabfrage sehr übersichtlich. Der Operator @> prüft, ob das angegebene Datum im Bereich enthalten ist, und behandelt die untere und obere Grenze automatisch.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_period
FROM employee_salary_v2
WHERE employee_id = 1
  AND valid_period @> '2021-03-15'::date;

Systemversionierte Tabellen (SQL-Standard)

Der Standard SQL:2011 führte systemversionierte temporale Tabellen ein. Die Datenbank verwaltet die Transaktionszeitspalten row_start und row_end automatisch. In PostgreSQL simulieren Sie dieses Verhalten; in SQL Server und MariaDB ist es mit SYSTEM VERSIONING integriert.

Das folgende Beispiel zeigt die Syntax von SQL Server / MariaDB als Referenz für das Konzept.

-- SQL Server / MariaDB syntax (reference)
CREATE TABLE dbo.Product (
  ProductID   INT PRIMARY KEY,
  Name        VARCHAR(100),
  Price       DECIMAL(10,2),
  SysStart    DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEnd      DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History));

Temporale Joins: Zwei Tabellen zeitlich ausrichten

Eine häufige Herausforderung besteht darin, zwei temporale Tabellen anhand übereinstimmender Zeiträume zu verbinden. Beispielsweise können Sie Mitarbeitergehälter mit Abteilungszuordnungen verbinden, wenn beide über Gültigkeitszeiträume verfügen. Verbinden Sie anhand des Entitätsschlüssels UND einer Bedingung für Überschneidungen, indem Sie && für Bereiche oder explizite Datumsvergleiche verwenden.

CREATE TABLE dept_assignment (
  employee_id INT,
  department  VARCHAR(50),
  valid_period DATERANGE
);

INSERT INTO dept_assignment VALUES
  (1, 'Engineering', '[2020-01-01, infinity)'),
  (1, 'Marketing',   '[2019-01-01, 2020-01-01)');

-- Periods where employee 1 was in Engineering AND had salary > 55000
SELECT s.salary, d.department,
       s.valid_period * d.valid_period AS overlap_period
FROM employee_salary_v2 s
JOIN dept_assignment d
  ON s.employee_id = d.employee_id
  AND s.valid_period && d.valid_period
WHERE s.employee_id = 1
  AND s.salary > 55000;

Lücken und Überschneidungen verhindern

Zwei häufige Probleme der Datenqualität in temporalen Tabellen sind Lücken (Zeiträume ohne Datensatz) und Überschneidungen (zwei gleichzeitig gültige Zeilen). Die Exclusion-Constraint mit && verhindert Überschneidungen auf Datenbankebene. Zum Erkennen von Lücken müssen Sie mit einer Abfrage nach fehlender Abdeckung suchen.

-- Find gaps in salary history for employee 1
-- (periods where upper(prev) < lower(next))
SELECT
  upper(a.valid_period) AS gap_start,
  lower(b.valid_period) AS gap_end
FROM employee_salary_v2 a
JOIN employee_salary_v2 b
  ON a.employee_id = b.employee_id
  AND upper(a.valid_period) < lower(b.valid_period)
WHERE a.employee_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM employee_salary_v2 c
    WHERE c.employee_id = 1
      AND lower(c.valid_period) > upper(a.valid_period)
      AND lower(c.valid_period) < lower(b.valid_period)
  )
ORDER BY gap_start;

Wissenscheck

Testen Sie Ihr Verständnis temporaler Tabellen und von Stichtagsabfragen.

Zusammenfassung: Temporale und versionierte Zeilen

In dieser Lektion haben Sie gelernt, wie Sie zeitabhängige Daten mit Spalten für die Gültigkeitszeit und dem PostgreSQL-Typ daterange modellieren. Wichtige Erkenntnisse:

  • Überschreiben Sie historische Zeilen niemals — schließen Sie die alte Zeile und fügen Sie eine neue Version ein.
  • Verwenden Sie Stichtagsabfragen (valid_from <= target AND valid_to > target), um Daten zu jedem beliebigen vergangenen Zeitpunkt abzurufen.
  • Der Typ daterange macht temporale Abfragen zusammen mit dem Operator @> kurz und übersichtlich.
  • Exclusion-Constraints auf && (Bereichsüberschneidung) erzwingen die Datenintegrität auf Datenbankebene.
  • Temporale Joins richten zwei Historien aus, indem sie ihre Gültigkeitszeiträume überschneiden.

Diese Muster bilden die Grundlage von Event Sourcing, Audit-Protokollierung und allen Systemen, in denen historische Genauigkeit von Bedeutung ist.

Häufig gestellte Fragen

Ist die Lektion „Temporale und versionierte Zeilen“ kostenlos?

Ja — der vollständige Text von „Temporale und versionierte Zeilen“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Temporale und versionierte Zeilen“?

Abfragen zur Gültigkeitszeit und zum Stand zu einem bestimmten Zeitpunkt Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „Temporale und versionierte Zeilen“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Warum Sie den Verlauf speichern sollten
  2. Eventtabellen mit ausschließlichem Anhängen
  3. Temporale und versionierte Zeilen
  4. Zustand aus Events rekonstruieren
← Zurück zu SQL Academy