はじめに
巡回展示会では、ニューヨークの Harbor Tickets とベルリンの Berlin Pop-Up でチケットを販売しています。各会場は、それぞれの現地午前 0 時に販売日の締めを行います。時計を切り替える日の現地の 1 日は、23 時間や 25 時間になることがあります。Harbor の 2 枚の領収書はどちらも 01:30 と表示されますが、UTC オフセットが異なるため、実際には異なる 2 つの瞬間と別々のチケットを表しています。
現地の 1 日の時間範囲と完全なチケット台帳を求める、再利用可能な PostgreSQL クエリを保存し、実際の締めレポートを CSV で用意します。基本的なターミナル操作、PostgreSQL 接続、SELECT/WHERE の知識が必要です。日付・時刻型、名前付きタイムゾーン、クエリのひな型、結合、エクスポート構文は使用前に説明します。PostgreSQL、元となる架空のチケットデータ、Terminal は準備済みです。Python やフロントエンドの開発は不要です。
/home/labex/project/day-close で windows.sql、daily-close.sql と、指定された close-YYYY-MM-DD.csv レポートを作成・編集します。元のテーブル、会場の ID・名前・タイムゾーン、チケットの ID・瞬間・金額・スタッフメモ、提供されたツールを保全してください。無料チケットもチケットとして数え、価格や表示時刻が同じでも、別々の領収書は区別します。これは簡略化したチケット販売の締め作業であり、税金、返金、決済事業者による精算、会計上の保証は扱いません。
元データのすべての瞬間には、明示的な UTC またはオフセット情報があります。固定された過去の日付と既知の現地午前 0 時を使います。オフセットのない、あいまいな表示時刻の解析は、この実習の範囲外です。実行環境のラベルと出力は英語のままです。
正しい現地日の時間範囲を作る
このステップでは、現地の 1 日が 24 時間だと仮定せず、各会場に指定された暦上の日付を、実際の 2 つの境界の瞬間に変換します。
Terminal の通常のシェルから始めます。./ は現在のディレクトリにある提供済みのプログラムを実行します。db ツールは読み取り専用の PostgreSQL 接続を開きます。desk は保存したクエリを実行し、実際の結果を表示します。まず 2 枚の領収書をニューヨーク時間で確認し、会場の設定を読み取ります。
ヒアドキュメントは、引用符付きの SQL マーカーから閉じる SQL までの行を db に渡します。閉じるマーカーは単独行にします。SQL 文はセミコロンで終わります。ブロックの終了時に接続は閉じられ、SET TIME ZONE はこの接続だけに影響します。次の ./db は UTC の新しい接続を開きます。WHERE ... IN (...) は列挙したチケット ID を残し、ORDER BY は 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
領収書には 2024-11-03 01:30:00-04 と 2024-11-03 01:30:00-05 が表示されます。オフセットは、共通の時間基準である UTC より、それぞれ 4 時間、5 時間遅いことを意味します。時計の表示は同じですが、2 つ目の瞬間は 1 時間後です。どちらも 500 セントでも、両方の ID を保ちます。
sold_at は PostgreSQL の timestamptz 型で、timestamp with time zone の略です。実際の瞬間を表し、接続の TimeZone で表示します。元のタイムゾーン名や、最初に記述されたオフセットを保管するものではありません。そのため各会場は、America/New_York のような IANA タイムゾーン名を別途持ちます。その日付に対応するルールによってオフセットが決まります。固定オフセットで、この名前を代用することはできません。
保存されている同じ瞬間を、新しい UTC 接続で読み取ります。ここでの -c は、引用符内の SQL 文字列全体を実行して終了します。
./db -c "SELECT ticket_id, sold_at FROM tickets WHERE ticket_id IN ('FOLD-1','FOLD-2') ORDER BY ticket_id;"
値はオフセット +00 の 05:30 と 06:30 です。表示タイムゾーンを変えても、チケットは変わっていません。
date は暦上の日付です。timestamp without time zone は時計の表示時刻であり、まだ世界全体で特定された瞬間ではありません。PostgreSQL の ::type は値を指定した型に変換します。日付を timestamp にすると、その日の 00:00 になります。この現地の timestamp に AT TIME ZONE を適用すると、会場の名前付きタイムゾーンで解釈し、timestamptz の瞬間を返します。日付に整数 1 を加えると、次の暦日になります。
Harbor の 11 月 3 日の販売日では、各午前 0 時を別々に変換します。
./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
UTC の開始は 11 月 3 日 04:00、終了は 11 月 4 日 05:00 で、経過時間は 25 時間です。春の切り替え日である 3 月 10 日は、23 時間しかありません。開始に固定の 24 時間を足すと、どちらの日も終了の瞬間を誤ります。タイムゾーンのオフセットは整数時間とは限らず、30 分だけの時計変更もあります。オフセットや長さをハードコードせず、両方の午前 0 時に適用される実際のルールを使ってください。
すべての会場に使えるクエリを保存します。WITH windows AS (...) は、中間クエリに名前を付ける共通テーブル式です。v は venues の短いテーブル別名です。AS は計算列の出力名を指定します。これにより、現地の暦に関する処理と表示形式を分けられます。
:'trading_date' は、指定された各日付について desk が渡す psql 変数です。コロンは置換を要求し、単一引用符は psql に値を SQL 文字列として引用させます。クエリ内に 1 つの日付を固定せず、この値を date に変換します。
最後の SELECT では、AT TIME ZONE 'UTC' は逆方向に働きます。timestamptz の瞬間を、UTC の時計表示に変換します。to_char はその表示形式を整えます。YYYY-MM-DD は年・月・日、HH24 は 24 時間表記、MI は分、SS は秒です。書式内で二重引用符に囲まれた T と Z は、そのまま出すラベルです。Z は UTC を意味するため、ラベルを付ける前に UTC に変換します。Z を表示するだけでは変換されません。
瞬間同士を引くと、経過時間の interval が得られます。EXTRACT(EPOCH FROM ...) はその総秒数を読み、3600 で割ると、必要に応じて端数も含む経過時間が時間単位で得られます。時の成分だけを読んでも、全体の長さにはなりません。
cat > windows.sql は、以下の完全なクエリファイルを書き込みます。引用符付きの SQL マーカーは、シェルによる本文の展開を防ぎます。閉じるマーカーを単独行に保ち、ブロック全体を貼り付けてください。
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
同じファイルに、desk を使って異なる販売日を渡します。--zone は、この呼び出しの接続の表示タイムゾーンを変えますが、会場の日付は変えません。
./desk windows 2024-11-03
./desk windows 2024-03-10
./desk windows 2024-11-03 --zone Asia/Tokyo
出力列は、venue_id、venue_name、time_zone、trading_date、opens_utc、closes_utc、hours の順です。Harbor の値は 25 時間と 23 時間で、Berlin はこれらの日付で 24 時間です。--zone が変えるのはデータベース接続の表示タイムゾーンであり、指定された会場の日付ではありません。明示的な UTC 境界は変わりません。
ファイル名やコマンドを打ち間違えたら、エラーを読み、ls で実際のファイルを確認して、修正したコマンドを再試行します。Day close error は実行エラーであり、正常な売上なしレポートではありません。クエリを直すには、保存した SQL を修正して再実行します。元のチケットデータを変更する必要はありません。実際の時間範囲を読んでからステップのチェックを実行します。
各会場の販売日に含まれる全チケットを保つ
このステップでは、現地日の境界を使い、条件を満たす元の各チケットをちょうど 1 回選び、売上のない会場も残します。
Terminal の /home/labex/project/day-close で続けます。windows.sql は保管します。1 日の時間範囲は半開区間です。開始の瞬間を含み、終了の瞬間を除きます。次の午前 0 時の領収書は翌日に属します。SQL の比較は、以上を表す >= と、厳密に小さいことを表す < です。BETWEEN は両端を含むため、終了時の領収書を隣接する両日に数えてしまいます。
先ほど確認した Harbor の時間範囲について、明示的な UTC 境界のリテラルを使って実際のチケットを確認します。ここでの -c は引用符内の SQL を実行して終了します。各リテラルの +00 が UTC の意味を明示します。
./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;"
この現地日には 6 枚のチケットが属します。時計の表示が繰り返された両領収書は残り、無料入場の amount_cents は 0 です。金額ゼロは、チケットがなかったことを意味しません。終了の午前 0 時の NEXT の領収書は、この日の範囲外です。その直前の LATE の領収書は、マイクロ秒も含めて範囲内です。排他的な終了境界を、1 秒を引くことで近似しないでください。
時間範囲の CTE を、再利用可能な台帳クエリに拡張します。結合は、2 つのデータ源の行を関連付けます。w は 1 つの時間範囲の行、t はチケットを参照します。LEFT JOIN は、ON 条件を満たすチケットがなくても時間範囲の行を残します。その場合、チケット列は一致する値がないことを意味する NULL になります。時間条件は ON に置いてください。最後の WHERE に移すと、売上のない会場が消えます。会場 ID の条件は、別会場の領収書が紐付くことを防ぎます。
実際の各チケットは、変更していない ID、UTC の瞬間、整数のセントを持つ 1 行になります。売上のない会場は、CSV 表現でチケット ID、販売の瞬間、金額が空欄の 1 行になります。これは空の会場の行であり、無料チケットではありません。すべての行に会場の識別情報、名前、タイムゾーン、日付を保ちます。
出力列は venue_id、venue_name、time_zone、trading_date、opens_utc、closes_utc、ticket_id、sold_utc、amount_cents の順です。UTC フィールドは、Z または同等のゼロオフセットで UTC を明示する必要があります。実際のチケットの瞬間を完全に保ってください。書式パターンの US はマイクロ秒の 6 桁を保ちます。null の販売時刻は、to_char を通しても null のままです。結果の行順は違ってもかまいませんが、必要なすべての行と値を残す必要があります。
完全なクエリを保存します。すでに練習したものと同じ日付パラメーターと、両午前 0 時の変換を使います。
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
desk の close アクションは、保存した SQL をそのまま実行し、実際の行と、その返された行だけから求めた集計を表示します。欠けたチケットの追加、正しい境界の選択、誤った価格の修正はしません。集計では、金額が 0 でも実際のチケット ID を数えます。秋、春、売上のない日付を指定します。
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
11 月 3 日は Harbor が 6 枚・合計 4750 セント、Berlin が 4 枚・合計 3750 セントです。3 月 10 日は Harbor が 4 枚・合計 3750 で、Berlin もチケット 0 枚・0 セントとして残ります。7 月 1 日は各会場に空の会場行があり、両集計ともゼロです。SQL エラーは、これらの正当な売上なしの行とは異なります。
同じ表示時刻、等しい価格、重複する会場名は、識別のルールではありません。元のチケット ID と会場 ID を保ち、DISTINCT で異なる領収書をまとめたり、台帳を合計だけに置き換えたりしないでください。合計だけでなく各行も確認してから、ステップのチェックを実行します。
意味が変わらないレポートを用意する
このステップでは、実際の締めクエリの行を 2 つの本物の CSV ファイルへエクスポートし、接続の表示タイムゾーンによって売上が別の会場日へ移らないことを確認します。
Terminal と day-close ディレクトリで続けます。両 SQL ファイルは保管します。--zone は、desk のその呼び出しだけについて PostgreSQL 接続の表示 TimeZone を指定します。保存された会場のタイムゾーン、販売日、元のチケットは変わりません。別の 2 つの接続タイムゾーンで、同じ締め対象日を比較します。
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
両出力には、引き続き同じ元のチケット、会場の日付、明示的な UTC の時間範囲、合計が含まれます。クエリが sold_at をそのまま date にキャストしたり、現地の時計表示を整形して Z と表示したりしていると、接続タイムゾーンの変更で誤りが明らかになる場合があります。データベースの全体設定や元の時刻を書き換えるのではなく、クエリを修正してください。
CSV はヘッダーと区切られた行を保存し、引用符によってカンマを含む値を保護します。desk の export アクションは、PostgreSQL 本来の CSV 出力を、この作業領域の close-YYYY-MM-DD.csv に書き込みます。ファイルがすでにあれば、その日付のレポートだけを置き換えます。書き込み前に正解計算でクエリを修復することはありません。read アクションは保存済みファイルだけを解析し、実際の行から表示用の集計を求めます。データベースへのクエリは行わず、省かれた行を元データから埋めることもありません。
説明した両日付をエクスポートし、実際のファイルを読み取ります。元のデータベースが利用できなくても、これらのレポートを保管して開き直せます。
./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
両ファイルは、説明した 9 列の台帳を保ちます。枚数と合計は、確認した実際のクエリ結果と一致します。Harbor/Berlin は秋が 6/4750 と 4/3750、春が 4/3750 と 0/0 です。繰り返された 01:30 の両 ID が秋のファイルに残り、無料チケットも実際の行として残ります。スクリーンショットや手入力の合計では、これらの事実を保全できません。
windows.sql、daily-close.sql、指定された両レポート、元のデータベースとツールを保管します。クエリを修正しても、古いファイルは自動的には更新されません。その日付を明示的に再エクスポートし、保存したファイルを読んで確認してください。レポートのファイル名を打ち間違えたら、データベースを変えるのではなく、ls を使って実際の名前で再試行します。保存した行を確認してから最終チェックを実行します。
まとめ
暦上の日付、時計の表示時刻、実際の瞬間、IANA タイムゾーンのルールを区別しました。現地の両方の午前 0 時を独立に変換し、半開区間によって元の各チケットを 1 回ずつ残し、売上のない会場も保ち、明示的な UTC の瞬間を持つ実際のレポートをエクスポートしました。保存したクエリはセッションの TimeZone が変わっても動作し、ファイルリーダーは実際に保存された行だけを表示します。これは簡略化した過去のチケット販売の締め作業であり、決済精算や、任意のあいまいな時刻の解析ではありません。
