Einführung
Die Wanderausstellung verkauft Tickets bei Harbor Tickets in New York und Berlin Pop-Up in Berlin. Jeder Veranstaltungsort schließt seinen Verkaufstag um seine eigene lokale Mitternacht ab. An einem Zeitumstellungstag kann dieser lokale Tag 23 oder 25 Stunden dauern. Zwei Harbor-Belege zeigen beide 01:30, doch ihre unterschiedlichen UTC-Offsets kennzeichnen zwei verschiedene tatsächliche Zeitpunkte und zwei verschiedene Tickets.
Du speicherst wiederverwendbare PostgreSQL-Abfragen für lokale Tageszeitfenster und vollständige Ticketlisten und lieferst anschließend echte CSV-Abschlussberichte ab. Du benötigst grundlegende Kenntnisse der Terminalnavigation, von PostgreSQL-Verbindungen und SELECT/WHERE. Datums-/Zeittypen, benannte Zeitzonen, Abfragevorlagen, Joins und Exportsyntax werden vor ihrer Verwendung erklärt. PostgreSQL, ursprüngliche synthetische Ticketdaten und Terminal sind vorbereitet; Python- oder Frontendentwicklung ist nicht erforderlich.
Erstelle oder bearbeite windows.sql, daily-close.sql und die angeforderten Berichte close-YYYY-MM-DD.csv unter /home/labex/project/day-close. Bewahre die ursprünglichen Tabellen, Veranstaltungsort-IDs/-Namen/-Zeitzonen, Ticket-IDs/-Zeitpunkte/-Beträge/-Personalnotizen und die bereitgestellten Werkzeuge. Kostenlose Tickets zählen weiterhin als Tickets, und separate Belege mit gleichen Preisen oder Uhrzeiten bleiben separat. Dies ist eine vereinfachte Übung zum Ticketverkaufsabschluss, ohne Steuern, Erstattungen, Zahlungsdienstleister-Abrechnung oder buchhalterische Garantien.
Alle Quellzeitpunkte haben ausdrückliche UTC-/Offsetangaben. Wir verwenden feste historische Daten und bekannte lokale Mitternachtszeitpunkte; das Einlesen mehrdeutiger Uhrzeiten ohne Offset liegt außerhalb dieser Übung. Laufzeitbeschriftungen und Ausgaben bleiben Englisch.
Die richtigen lokalen Tageszeitfenster erstellen
In diesem Schritt wandelst du das angeforderte Kalenderdatum jedes Veranstaltungsorts in zwei tatsächliche Grenzzeitpunkte um, ohne anzunehmen, dass ein lokaler Tag 24 Stunden dauert.
Beginne in Terminal an der normalen Shell. ./ führt ein bereitgestelltes Programm im aktuellen Verzeichnis aus. Das Werkzeug db öffnet eine schreibgeschützte PostgreSQL-Verbindung; desk führt deine gespeicherten Abfragen aus und zeigt deren tatsächliche Ergebnisse. Prüfe zuerst die beiden Belege in New Yorker Zeit und lies die Einstellungen der Veranstaltungsorte:
Ein Here-Dokument sendet die Zeilen zwischen der Markierung SQL in Anführungszeichen und dem abschließenden SQL an db. Die Endmarkierung muss allein in ihrer Zeile stehen. SQL-Anweisungen enden mit Semikolons. Diese Verbindung schließt sich am Ende des Blocks; ihr SET TIME ZONE betrifft nur diese Verbindung. Das nächste ./db startet eine neue Verbindung in UTC. WHERE ... IN (...) behält die aufgelisteten Ticket-IDs bei, und ORDER BY sortiert die Anzeige nach ID.
cd /home/labex/project/day-close
./db <<'SQL'
SET TIME ZONE 'America/New_York';
SELECT ticket_id, sold_at, amount_cents
FROM tickets
WHERE ticket_id IN ('FOLD-1', 'FOLD-2')
ORDER BY ticket_id;
SELECT venue_id, venue_name, time_zone FROM venues ORDER BY venue_id;
SQL
Die Belege zeigen 2024-11-03 01:30:00-04 und 2024-11-03 01:30:00-05. Die Offsets bedeuten vier und fünf Stunden hinter UTC, der gemeinsamen Zeitreferenz. Die Uhr zeigt dasselbe, doch der zweite Zeitpunkt liegt eine Stunde später. Bewahre beide IDs, obwohl beide Tickets 500 Cent kosten.
sold_at hat den PostgreSQL-Typ timestamptz, kurz für timestamp with time zone. Er stellt einen Zeitpunkt dar und zeigt ihn in der TimeZone der Verbindung an. Er bewahrt weder den ursprünglichen Zeitzonennamen noch den ursprünglich geschriebenen Offset. Jeder Veranstaltungsort hat deshalb einen separaten IANA-Zeitzonennamen, etwa America/New_York, dessen datumsspezifische Regeln den Offset bestimmen. Ein fester Offset ist kein Ersatz für diesen Namen.
Lies dieselben gespeicherten Zeitpunkte in einer neuen UTC-Verbindung. Hier führt -c die vollständige SQL-Zeichenfolge in Anführungszeichen aus und beendet sich:
./db -c "SELECT ticket_id, sold_at FROM tickets WHERE ticket_id IN ('FOLD-1','FOLD-2') ORDER BY ticket_id;"
Die Werte sind 05:30 und 06:30 mit dem Offset +00. Das Ändern einer Anzeigezeitzone hat die Tickets nicht verändert.
Ein date ist eine Kalenderbezeichnung. Ein timestamp without time zone ist eine Uhrzeit, noch kein global bestimmter Zeitpunkt. PostgreSQLs ::type wandelt einen Wert in einen Typ um. Das Umwandeln eines Datums in timestamp ergibt dieses Datum um 00:00. Auf diesen lokalen Zeitstempel angewendet, interpretiert AT TIME ZONE ihn anhand der benannten Zeitzone des Veranstaltungsorts und gibt einen timestamptz-Zeitpunkt zurück. Datum plus Ganzzahl 1 bedeutet das nächste Kalenderdatum.
Rechne für Harbors Verkaufstag am 3. November jede Mitternacht separat um:
./db <<'SQL'
SELECT '2024-11-03'::date::timestamp AT TIME ZONE 'America/New_York' AS opens_at,
('2024-11-03'::date + 1)::timestamp AT TIME ZONE 'America/New_York' AS closes_at;
SQL
Der UTC-Beginn ist am 3. November um 04:00 und das UTC-Ende am 4. November um 05:00: 25 verstrichene Stunden. Die Frühjahrsumstellung am 10. März hat nur 23 verstrichene Stunden. Feste 24 Stunden zum Beginn zu addieren, ergäbe an beiden Tagen den falschen Endzeitpunkt. Zeitzonenoffsets müssen keine ganzen Stunden sein, und manche Zeitumstellungen betragen nur eine halbe Stunde; verwende die tatsächlichen Regeln an beiden Mitternachtsgrenzen, statt einen Offset oder eine Dauer fest zu kodieren.
Speichere eine wiederverwendbare Abfrage für alle Veranstaltungsorte. WITH windows AS (...) gibt einer Zwischenabfrage einen Namen; sie heißt Common Table Expression. v ist ein kurzer Tabellenalias für venues. AS gibt einer berechneten Spalte ihren Ausgabenamen. Dies trennt lokale Kalenderberechnungen von der Anzeigeformatierung.
:'trading_date' ist eine psql-Variable, die das Arbeitsplatzprogramm für jedes angeforderte Datum liefert. Der Doppelpunkt fordert die Ersetzung an, und die einfachen Anführungszeichen lassen psql den Wert als SQL-Zeichenfolge in Anführungszeichen setzen. Wandle diesen Wert in date um, statt ein Datum in der Abfrage festzuschreiben.
Im abschließenden SELECT wirkt AT TIME ZONE 'UTC' in die andere Richtung: Es wandelt einen timestamptz-Zeitpunkt in seine UTC-Uhrzeitdarstellung um. to_char formatiert diese Darstellung. YYYY-MM-DD ist Jahr/Monat/Tag, HH24 eine 24-Stunden-Uhr, MI bedeutet Minuten und SS Sekunden. T und Z in doppelten Anführungszeichen im Format sind wörtliche Kennzeichnungen. Z bedeutet UTC, also rechne vor dem Ergänzen dieser Kennzeichnung in UTC um; Z allein auszugeben, rechnet nichts um.
Das Subtrahieren der Zeitpunkte ergibt ein verstrichenes Intervall. EXTRACT(EPOCH FROM ...) liest dessen gesamte Sekunden; das Teilen durch 3600 ergibt verstrichene Stunden, gegebenenfalls einschließlich eines Stundenbruchteils. Nur die Stundenkomponente zu lesen, ergäbe nicht die vollständige Dauer.
cat > windows.sql schreibt die vollständige Abfragedatei unten. Die Markierung SQL in Anführungszeichen verhindert, dass die Shell den Inhalt expandiert. Füge den gesamten Block ein und belasse die Endmarkierung allein in ihrer Zeile:
cat > windows.sql <<'SQL'
WITH windows AS (
SELECT v.venue_id, v.venue_name, v.time_zone,
:'trading_date'::date AS trading_date,
:'trading_date'::date::timestamp AT TIME ZONE v.time_zone AS opens_at,
(:'trading_date'::date + 1)::timestamp AT TIME ZONE v.time_zone AS closes_at
FROM venues AS v
)
SELECT venue_id, venue_name, time_zone, trading_date,
to_char(opens_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS opens_utc,
to_char(closes_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS closes_utc,
EXTRACT(EPOCH FROM (closes_at - opens_at)) / 3600 AS hours
FROM windows
ORDER BY venue_id;
SQL
Verwende das Arbeitsplatzprogramm, um derselben Datei unterschiedliche Verkaufsdaten zu übergeben. --zone ändert die Anzeigezeitzone der Verbindung für diesen Aufruf, nicht das Datum des Veranstaltungsorts:
./desk windows 2024-11-03
./desk windows 2024-03-10
./desk windows 2024-11-03 --zone Asia/Tokyo
Die Ausgabespalten sind venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc und hours, in dieser Reihenfolge. Harbors Werte sind 25 und 23 Stunden; Berlin hat an diesen Daten 24. --zone ändert die Anzeigezeitzone der Datenbankverbindung, nicht das angeforderte Datum des Veranstaltungsorts. Ausdrückliche UTC-Grenzen bleiben gleich.
Vertippst du dich bei einem Dateinamen oder Befehl, lies den Fehler, prüfe mit ls die tatsächlichen Dateien und wiederhole den korrigierten Befehl. Ein Day close error ist ein Ausführungsfehler, kein erfolgreicher Bericht ohne Verkäufe. Korrigiere zum Reparieren einer Abfrage das gespeicherte SQL und führe es erneut aus; ursprüngliche Ticketdaten müssen nicht verändert werden. Führe nach dem Lesen der tatsächlichen Zeitfenster die Schrittprüfung aus.
Jedes Ticket innerhalb des Tages seines Veranstaltungsorts behalten
In diesem Schritt verwendest du die lokalen Tagesgrenzen, um jedes geeignete ursprüngliche Ticket genau einmal auszuwählen und dabei Veranstaltungsorte ohne Verkäufe beizubehalten.
Bleibe in Terminal und /home/labex/project/day-close. Bewahre windows.sql. Ein Tageszeitfenster ist halboffen: Es schließt seinen Beginn ein und sein Ende aus. Ein Beleg genau zur nächsten Mitternacht gehört zum nächsten Tag. Die SQL-Vergleiche sind >= für größer oder gleich und < für strikt kleiner. BETWEEN schließt beide Endpunkte ein und würde daher einen Beleg zum Endzeitpunkt in zwei benachbarten Tagen zählen.
Prüfe für das gerade beobachtete Harbor-Zeitfenster die tatsächlichen Tickets mit ausdrücklichen UTC-Grenzliteralen. Hier führt -c das SQL in Anführungszeichen aus und beendet sich; das +00 jedes Literals macht dessen UTC-Bedeutung ausdrücklich:
./db -c "SELECT ticket_id, sold_at, amount_cents FROM tickets WHERE venue_id='HARBOR' AND sold_at >= '2024-11-03 04:00:00+00'::timestamptz AND sold_at < '2024-11-04 05:00:00+00'::timestamptz ORDER BY sold_at,ticket_id;"
Sechs Tickets gehören zu diesem lokalen Tag. Beide Belege mit wiederholter Uhrzeit bleiben vorhanden; der kostenlose Eintritt hat amount_cents gleich 0. Ein Betrag von null bedeutet nicht, dass es kein Ticket gab. Der Beleg NEXT zur abschließenden Mitternacht liegt außerhalb dieses Tages. Der Beleg LATE direkt davor liegt darin, einschließlich seiner Mikrosekunden. Nähere das exklusive Ende nicht durch das Abziehen einer Sekunde an.
Erweitere die Zeitfenster-CTE zu einer wiederverwendbaren Ticketlistenabfrage. Ein Join ordnet Zeilen aus zwei Quellen einander zu. w verweist auf eine Zeitfensterzeile und t auf ein Ticket. LEFT JOIN behält ein Zeitfenster bei, auch wenn kein Ticket seine ON-Bedingungen erfüllt. Seine Ticketspalten sind dann NULL, also ohne passenden Wert. Setze die Zeitbedingungen in ON; sie in ein abschließendes WHERE zu verschieben, würde den leeren Veranstaltungsort entfernen. Die Bedingung für die Veranstaltungsort-ID verhindert, dass ein Veranstaltungsort die Belege eines anderen erhält.
Jedes tatsächliche Ticket erzeugt eine Zeile mit seiner unveränderten ID, seinem UTC-Zeitpunkt und ganzzahligen Centbetrag. Ein leerer Veranstaltungsort erzeugt eine Zeile mit leerer Ticket-ID, leerem Verkaufszeitpunkt und leerem Betrag in der CSV-Darstellung. Dies ist eine Zeile für einen leeren Veranstaltungsort, kein Ticket mit Preis null. Bewahre Veranstaltungsortidentität, Name, Zeitzone und Datum in jeder Zeile.
Die Ausgabespalten sind der Reihe nach venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc, ticket_id, sold_utc, amount_cents. UTC-Felder müssen UTC ausdrücklich kennzeichnen, mit Z oder einem gleichwertigen Nulloffset. Der vollständige Zeitpunkt eines tatsächlichen Tickets muss erhalten bleiben; US im Formatmuster bewahrt sechs Mikrosekundenstellen. Verkaufszeitpunkte mit dem Wert NULL bleiben auch nach to_char NULL. Die Reihenfolge der Ergebniszeilen darf abweichen, doch jede erforderliche Zeile und jeder Wert müssen erhalten bleiben.
Schreibe die vollständige gespeicherte Abfrage. Sie verwendet denselben Datumsparameter und dieselben zwei Mitternachtsumrechnungen, die du bereits geübt hast:
cat > daily-close.sql <<'SQL'
WITH windows AS (
SELECT v.venue_id, v.venue_name, v.time_zone,
:'trading_date'::date AS trading_date,
:'trading_date'::date::timestamp AT TIME ZONE v.time_zone AS opens_at,
(:'trading_date'::date + 1)::timestamp AT TIME ZONE v.time_zone AS closes_at
FROM venues AS v
)
SELECT w.venue_id, w.venue_name, w.time_zone, w.trading_date,
to_char(w.opens_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS opens_utc,
to_char(w.closes_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS closes_utc,
t.ticket_id,
to_char(t.sold_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS sold_utc,
t.amount_cents
FROM windows AS w
LEFT JOIN tickets AS t
ON t.venue_id = w.venue_id
AND t.sold_at >= w.opens_at
AND t.sold_at < w.closes_at
ORDER BY w.venue_id, t.sold_at, t.ticket_id;
SQL
Die Aktion close des Arbeitsplatzprogramms führt das gespeicherte SQL unverändert aus und zeigt dessen tatsächliche Zeilen sowie eine nur aus diesen zurückgegebenen Zeilen abgeleitete Zusammenfassung an. Sie ergänzt keine fehlenden Tickets, wählt keine korrekte Grenze aus und korrigiert keinen falschen Preis. Die Zusammenfassung zählt eine tatsächliche Ticket-ID auch bei einem Betrag von 0. Frage Herbst, Frühjahr und ein Datum ohne Verkäufe ab:
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
Am 3. November hat Harbor sechs Tickets mit insgesamt 4750 Cent; Berlin hat vier mit insgesamt 3750. Am 10. März hat Harbor vier mit insgesamt 3750, und Berlin erscheint weiterhin mit null Tickets und null Cent. Der 1. Juli hat eine Leerzeile für jeden Veranstaltungsort, und beide Zusammenfassungen sind null. Ein SQL-Fehler unterscheidet sich von diesen gültigen Zeilen ohne Verkäufe.
Gleiche Uhrzeiten, gleiche Preise und doppelte Veranstaltungsortnamen sind keine Identitätsregeln. Bewahre ursprüngliche Ticket- und Veranstaltungsort-IDs; fasse unterschiedliche Belege nicht mit DISTINCT zusammen und ersetze die Ticketliste nicht durch eine Summe allein. Führe die Schrittprüfung aus, nachdem du sowohl die Zeilen als auch die Summen geprüft hast.
Berichte mit unveränderter Bedeutung übergeben
In diesem Schritt exportierst du die tatsächlichen Zeilen der Abschlussabfrage in zwei echte CSV-Dateien und bestätigst, dass die Anzeigezeitzone einer Verbindung einen Verkauf nicht in einen anderen lokalen Tag verschiebt.
Bleibe in Terminal und im day-close-Verzeichnis. Bewahre beide SQL-Dateien. --zone wählt die Anzeige-TimeZone der PostgreSQL-Verbindung für diesen einzelnen Aufruf des Arbeitsplatzprogramms. Es ändert weder die gespeicherte Zeitzone des Veranstaltungsorts noch dessen Verkaufsdatum oder die Quelltickets. Vergleiche denselben abgeschlossenen Tag unter zwei anderen Verbindungszeitzonen:
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
Beide Ausgaben enthalten weiterhin dieselben ursprünglichen Tickets, Veranstaltungsortdaten, ausdrücklichen UTC-Zeitfenster und Summen. Verwendet eine Abfrage eine bloße Typumwandlung von sold_at in date oder formatiert sie eine lokale Uhrzeit und kennzeichnet sie mit Z, kann ein Wechsel der Verbindungszeitzone den Fehler aufdecken. Korrigiere die Abfrage, statt globale Datenbankeinstellungen zu ändern oder Quellzeiten umzuschreiben.
CSV speichert eine Kopfzeile und durch Trennzeichen getrennte Zeilen; Anführungszeichen schützen Werte mit Kommas. Die Aktion export des Arbeitsplatzprogramms schreibt die native PostgreSQL-CSV-Ausgabe nach close-YYYY-MM-DD.csv in diesem Arbeitsbereich. Sie ersetzt nur den Bericht dieses Datums, falls die Datei bereits existiert. Keine Referenzberechnung repariert die Abfrage vor dem Schreiben. Die Aktion read liest nur die gespeicherte Datei ein und leitet ihre angezeigte Zusammenfassung aus diesen tatsächlichen Zeilen ab; sie führt keine Datenbankabfrage aus und ergänzt niemals ausgelassene Zeilen aus der Quelle.
Exportiere beide dokumentierten Daten und lies anschließend ihre tatsächlichen Dateien. Du kannst diese Berichte aufbewahren und erneut öffnen, wenn die Quelldatenbank nicht verfügbar ist:
./desk export 2024-11-03
./desk export 2024-03-10
ls
./desk read close-2024-11-03.csv
./desk read close-2024-03-10.csv
Die beiden Dateien bewahren die dokumentierte Ticketliste mit neun Spalten. Ihre Anzahlen und Summen stimmen mit den tatsächlichen Abfrageergebnissen überein, die du geprüft hast: 6/4750 und 4/3750 im Herbst, dann 4/3750 und 0/0 im Frühjahr für Harbor/Berlin. Beide IDs für die wiederholte 01:30 bleiben in der Herbstdatei, und das kostenlose Ticket bleibt eine tatsächliche Zeile. Ein Screenshot oder eine von Hand eingegebene Summe würde diese Fakten nicht bewahren.
Bewahre windows.sql, daily-close.sql, beide angeforderten Berichte und die ursprüngliche Datenbank/Werkzeuge. Eine Abfragekorrektur aktualisiert eine ältere Datei nicht stillschweigend: Exportiere dieses Datum ausdrücklich erneut und lies dann die gespeicherte Datei zur Kontrolle. Vertippst du dich beim Berichtsdateinamen, verwende ls und versuche es mit dem tatsächlichen Namen erneut, statt die Datenbank zu verändern. Führe nach dem Bestätigen der gespeicherten Zeilen die Abschlussprüfung aus.
Zusammenfassung
Du hast Kalenderbezeichnungen, Uhrzeiten, Zeitpunkte und IANA-Zeitzonenregeln voneinander getrennt. Du hast beide lokalen Mitternachtsgrenzen unabhängig umgerechnet, mit einem halboffenen Zeitfenster jedes ursprüngliche Ticket genau einmal bewahrt, leere Veranstaltungsorte beibehalten und echte Berichte mit ausdrücklichen UTC-Zeitpunkten exportiert. Deine gespeicherten Abfragen funktionieren über Änderungen der Sitzungs-TimeZone hinweg, und dein Dateileseprogramm zeigt nur die tatsächlich gespeicherten Zeilen. Dies sind vereinfachte historische Ticketabschlüsse, keine Zahlungsabwicklung und kein Einlesen beliebiger mehrdeutiger Zeitangaben.
