Introduction
L’exposition itinérante vend des billets chez Harbor Tickets à New York et Berlin Pop-Up à Berlin. Chaque site clôture une journée de vente à son propre minuit local. Un jour de changement d’heure, cette journée locale peut durer 23 ou 25 heures. Deux reçus de Harbor affichent tous deux 01:30, mais leurs décalages UTC différents identifient deux instants réels différents et deux billets différents.
Vous enregistrerez des requêtes PostgreSQL réutilisables pour les plages de journées locales et les registres complets de billets, puis livrerez de véritables rapports de clôture CSV. Vous devez savoir naviguer dans un terminal, vous connecter à PostgreSQL et utiliser SELECT/WHERE. Les types de date/heure, les fuseaux nommés, les modèles de requête, les jointures et la syntaxe d’export sont expliqués avant utilisation. PostgreSQL, les données fictives d’origine des billets et Terminal sont prêts ; aucun développement Python ni d’interface web n’est requis.
Créez ou modifiez windows.sql, daily-close.sql et les rapports close-YYYY-MM-DD.csv demandés dans /home/labex/project/day-close. Conservez les tables d’origine, les identifiants/noms/fuseaux des sites, les identifiants/instants/montants/notes du personnel des billets et les outils fournis. Les billets gratuits comptent toujours comme des billets, et des reçus distincts ayant des prix ou des heures affichées identiques restent distincts. Il s’agit d’un exercice simplifié de clôture des ventes de billets, sans taxes, remboursements, règlement par un prestataire de paiement ni garanties comptables.
Tous les instants sources comportent des informations UTC/de décalage explicites. Nous utilisons des dates historiques fixes et des minuits locaux connus ; l’analyse d’heures ambiguës sans décalage sort du périmètre de cet exercice. Les libellés et les sorties de l’environnement d’exécution restent en anglais.
Construire les bonnes plages de journées locales
Dans cette étape, vous transformerez la date calendaire demandée de chaque site en deux véritables instants de délimitation, sans supposer qu’une journée locale dure 24 heures.
Commencez dans Terminal, à l’invite du shell ordinaire. ./ exécute un programme fourni dans le répertoire courant. L’outil db ouvre une connexion PostgreSQL en lecture seule ; desk exécutera vos requêtes enregistrées et affichera leurs véritables résultats. Examinez d’abord les deux reçus à l’heure de New York et lisez les paramètres des sites :
Un here-document envoie à db les lignes situées entre le marqueur SQL entre guillemets et le SQL final. Le marqueur final doit être seul sur sa ligne. Les instructions SQL se terminent par des points-virgules. Cette connexion se ferme à la fin du bloc ; son SET TIME ZONE n’affecte que cette connexion. Le prochain ./db démarre une nouvelle connexion en UTC. WHERE ... IN (...) conserve les identifiants de billet énumérés et ORDER BY trie l’affichage par identifiant.
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
Les reçus affichent 2024-11-03 01:30:00-04 et 2024-11-03 01:30:00-05. Les décalages signifient quatre et cinq heures de retard sur UTC, la référence temporelle commune. L’horloge indique la même heure, mais le second instant est une heure plus tard. Conservez les deux identifiants, même si les deux billets coûtent 500 centimes.
sold_at possède le type PostgreSQL timestamptz, abréviation de timestamp with time zone. Il représente un instant et l’affiche dans le TimeZone de la connexion. Il ne conserve ni le nom du fuseau d’origine ni le décalage écrit à l’origine. Chaque site possède donc un nom de fuseau IANA distinct, comme America/New_York, dont les règles propres à la date déterminent le décalage. Un décalage fixe ne remplace pas ce nom.
Lisez les mêmes instants enregistrés dans une nouvelle connexion UTC. Ici, -c exécute la chaîne SQL complète entre guillemets puis quitte :
./db -c "SELECT ticket_id, sold_at FROM tickets WHERE ticket_id IN ('FOLD-1','FOLD-2') ORDER BY ticket_id;"
Les valeurs sont 05:30 et 06:30 avec le décalage +00. Changer le fuseau d’affichage n’a pas modifié les billets.
Une date est une étiquette calendaire. Un timestamp without time zone est une heure d’horloge, pas encore un instant identifié à l’échelle mondiale. Le ::type de PostgreSQL convertit une valeur vers un type. Convertir une date en timestamp donne cette date à 00:00. Appliqué à cet horodatage local, AT TIME ZONE l’interprète dans le fuseau nommé du site et renvoie un instant timestamptz. Une date plus l’entier 1 signifie la date calendaire suivante.
Pour la journée de vente du 3 novembre de Harbor, convertissez chaque minuit séparément :
./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
L’ouverture UTC est le 3 novembre à 04:00 et la clôture UTC le 4 novembre à 05:00 : 25 heures écoulées. Le changement de printemps du 10 mars ne comporte que 23 heures écoulées. Ajouter 24 heures fixes à l’ouverture donnerait un mauvais instant de clôture pour les deux journées. Les décalages des fuseaux ne sont pas forcément des heures entières et certains changements d’heure ne sont que d’une demi-heure ; utilisez les véritables règles aux deux minuits plutôt que de coder en dur un décalage ou une durée.
Enregistrez une requête réutilisable pour tous les sites. WITH windows AS (...) donne un nom à une requête intermédiaire, appelée expression de table commune. v est un alias court de table pour venues. AS donne à une colonne calculée son nom de sortie. Cela sépare le travail sur le calendrier local de la mise en forme de l’affichage.
:'trading_date' est une variable psql fournie par le poste pour chaque date demandée. Les deux-points demandent la substitution et les guillemets simples font que psql cite la valeur comme une chaîne SQL. Convertissez cette valeur en date au lieu de fixer une date dans la requête.
Dans le SELECT final, AT TIME ZONE 'UTC' agit dans l’autre sens : il convertit un instant timestamptz en sa représentation d’horloge UTC. to_char met en forme cette représentation. YYYY-MM-DD est année/mois/jour, HH24 est une horloge sur 24 heures, MI signifie minutes et SS secondes. T et Z entre guillemets doubles dans le format sont des étiquettes littérales. Z signifie UTC : convertissez donc en UTC avant d’ajouter cette étiquette ; afficher Z seul ne convertit rien.
Soustraire les instants donne un intervalle écoulé. EXTRACT(EPOCH FROM ...) lit son nombre total de secondes ; diviser par 3600 donne les heures écoulées, y compris une fraction d’heure le cas échéant. Lire uniquement la composante des heures ne donnerait pas la durée complète.
cat > windows.sql écrit le fichier de requête complet ci-dessous. Le marqueur SQL entre guillemets empêche le shell d’effectuer des expansions dans le corps. Collez tout le bloc en gardant le marqueur final seul sur sa ligne :
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
Utilisez le poste pour fournir différentes dates de vente au même fichier. --zone change le fuseau d’affichage de la connexion pour cet appel, pas la date du site :
./desk windows 2024-11-03
./desk windows 2024-03-10
./desk windows 2024-11-03 --zone Asia/Tokyo
Les colonnes de sortie sont venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc et hours, dans cet ordre. Les valeurs de Harbor sont 25 et 23 heures ; Berlin a 24 heures à ces dates. --zone change le fuseau d’affichage de la connexion à la base, pas la date demandée du site. Les limites UTC explicites restent identiques.
Si vous faites une faute dans un nom de fichier ou une commande, lisez l’erreur, utilisez ls pour examiner les véritables fichiers et réessayez la commande corrigée. Une erreur Day close error est une erreur d’exécution, pas un rapport sans vente réussi. Pour réparer une requête, corrigez le SQL enregistré et relancez-le ; les données d’origine des billets n’ont pas besoin de changer. Lancez la vérification de l’étape après avoir lu les véritables plages.
Conserver chaque billet dans la journée de son site
Dans cette étape, vous utiliserez les limites de la journée locale pour sélectionner chaque billet d’origine admissible exactement une fois, tout en conservant les sites qui n’ont eu aucune vente.
Restez dans Terminal et dans /home/labex/project/day-close. Conservez windows.sql. Une plage de journée est semi-ouverte : incluez son instant d’ouverture et excluez son instant de clôture. Un reçu au minuit suivant appartient à la journée suivante. Les comparaisons SQL sont >= pour supérieur ou égal et < pour strictement inférieur. BETWEEN inclut les deux bornes ; il compterait donc un reçu à la clôture dans deux journées adjacentes.
Pour la plage de Harbor que vous venez d’observer, examinez les véritables billets avec des littéraux de limites UTC explicites. Ici, -c exécute le SQL entre guillemets puis quitte ; le +00 de chaque littéral rend son sens UTC explicite :
./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;"
Six billets appartiennent à cette journée locale. Les deux reçus à heure répétée restent présents ; l’entrée gratuite a amount_cents égal à 0. Un montant nul ne signifie pas qu’il n’y a pas eu de billet. Le reçu NEXT au minuit de clôture est hors de cette journée. Le reçu LATE, juste avant, est dedans, y compris ses microsecondes. Ne cherchez pas à représenter approximativement la fin exclusive en soustrayant une seconde.
Étendez la CTE des plages pour en faire une requête de registre réutilisable. Une jointure associe des lignes de deux sources. w désigne une ligne de plage et t un billet. LEFT JOIN conserve une plage même si aucun billet ne satisfait ses conditions ON. Ses colonnes de billet sont alors NULL, ce qui signifie aucune valeur correspondante. Placez les conditions temporelles dans ON ; les déplacer dans un WHERE final supprimerait le site sans vente. La condition sur l’identifiant de site empêche un site de recevoir les reçus d’un autre.
Chaque véritable billet produit une ligne avec son identifiant inchangé, son instant UTC et ses centimes entiers. Un site sans vente produit une ligne dont l’identifiant de billet, l’instant de vente et le montant sont vides dans la représentation CSV. C’est une ligne de site sans vente, pas un billet à prix nul. Conservez l’identité, le nom, le fuseau et la date du site sur chaque ligne.
Les colonnes de sortie, dans l’ordre, sont venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc, ticket_id, sold_utc, amount_cents. Les champs UTC doivent identifier explicitement UTC, avec Z ou un décalage nul équivalent. L’instant complet d’un véritable billet doit être conservé ; US dans le modèle de format préserve six chiffres de microsecondes. Les instants de vente NULL restent NULL lors du passage dans to_char. L’ordre des lignes du résultat peut varier, mais chaque ligne et valeur requise doit rester présente.
Écrivez la requête enregistrée complète. Elle utilise le même paramètre de date et les conversions des deux minuits déjà pratiquées :
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
L’action close du poste exécute le SQL enregistré sans le modifier et affiche ses véritables lignes ainsi qu’un récapitulatif dérivé uniquement de ces lignes renvoyées. Elle n’ajoute pas de billets manquants, ne choisit pas une limite correcte et ne corrige pas un mauvais prix. Le récapitulatif compte un véritable identifiant de billet même si son montant est 0. Demandez l’automne, le printemps et une date sans vente :
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
Pour le 3 novembre, Harbor a six billets totalisant 4750 centimes ; Berlin en a quatre totalisant 3750. Pour le 10 mars, Harbor en a quatre totalisant 3750 et Berlin apparaît toujours avec zéro billet et zéro centime. Le 1er juillet a une ligne de site sans vente pour chaque site et les deux récapitulatifs indiquent zéro. Une erreur SQL diffère de ces lignes sans vente valides.
Des heures identiques, des prix égaux et des noms de sites en double ne sont pas des règles d’identité. Conservez les identifiants d’origine des billets et des sites ; ne fusionnez pas des reçus différents avec DISTINCT et ne remplacez pas le registre par un seul total. Lancez la vérification de l’étape après avoir examiné les lignes autant que les totaux.
Livrer des rapports qui conservent leur sens
Dans cette étape, vous exporterez les véritables lignes de la requête de clôture dans deux véritables fichiers CSV et confirmerez que le fuseau d’affichage d’une connexion ne déplace pas une vente vers une autre journée de site.
Restez dans Terminal et dans le répertoire day-close. Conservez les deux fichiers SQL. --zone choisit le TimeZone d’affichage de la connexion PostgreSQL pour ce seul appel du poste. Il ne change ni le fuseau enregistré du site, ni sa date de vente, ni les billets sources. Comparez la même journée clôturée avec deux autres fuseaux de connexion :
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
Les deux sorties contiennent toujours les mêmes billets d’origine, dates de site, plages UTC explicites et totaux. Si une requête utilise une simple conversion de sold_at en date ou met en forme une heure locale en l’étiquetant Z, changer le fuseau de connexion peut révéler l’erreur. Corrigez la requête plutôt que de changer les paramètres globaux de la base ou de réécrire les instants sources.
CSV stocke un en-tête et des lignes délimitées ; les guillemets protègent les valeurs contenant des virgules. L’action export du poste écrit la sortie CSV native de PostgreSQL dans close-YYYY-MM-DD.csv dans cet espace de travail. Elle remplace uniquement le rapport de cette date si le fichier existe déjà. Aucun calcul de référence ne répare la requête avant l’écriture. L’action read analyse uniquement le fichier enregistré et dérive son récapitulatif affiché de ces véritables lignes ; elle n’effectue aucune requête à la base et ne complète jamais les lignes omises à partir de la source.
Exportez les deux dates documentées, puis lisez leurs véritables fichiers. Vous pouvez conserver et rouvrir ces rapports lorsque la base source est indisponible :
./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
Les deux fichiers conservent le registre documenté à neuf colonnes. Leurs nombres de billets et totaux correspondent aux véritables résultats de requête que vous avez examinés : 6/4750 et 4/3750 en automne, puis 4/3750 et 0/0 au printemps pour Harbor/Berlin. Les deux identifiants de l’heure répétée 01:30 restent dans le fichier d’automne et le billet gratuit reste une véritable ligne. Une capture d’écran ou un total saisi à la main ne préserverait pas ces faits.
Conservez windows.sql, daily-close.sql, les deux rapports demandés et la base/les outils d’origine. Une correction de requête ne met pas silencieusement à jour un ancien fichier : exportez explicitement cette date à nouveau, puis lisez le fichier enregistré pour le vérifier. Si vous faites une faute dans un nom de rapport, utilisez ls et réessayez le véritable nom plutôt que de changer la base. Lancez la vérification finale après avoir confirmé les lignes enregistrées.
Résumé
Vous avez distingué les dates calendaires, les heures affichées, les instants et les règles des fuseaux IANA. Vous avez converti indépendamment les deux minuits locaux, utilisé une plage semi-ouverte pour conserver chaque billet d’origine une seule fois, préservé les sites sans vente et exporté de véritables rapports avec des instants UTC explicites. Vos requêtes enregistrées fonctionnent malgré les changements de TimeZone de session et votre lecteur de fichiers n’affiche que les véritables lignes enregistrées. Ce sont des clôtures historiques simplifiées de ventes de billets, pas un règlement de paiements ni une analyse d’heures ambiguës quelconques.
