Introduction
The touring exhibition sells tickets at Harbor Tickets in New York and Berlin Pop-Up in Berlin. Each venue closes a sales day at its own local midnight. On a clock-change day, that local day can last 23 or 25 hours. Two Harbor receipts both show 01:30, yet their different UTC offsets identify two different actual instants and two different tickets.
You will save reusable PostgreSQL queries for local-day windows and complete ticket ledgers, then deliver real CSV closing reports. You need basic terminal navigation, PostgreSQL connections and SELECT/WHERE. Date/time types, named zones, query templates, joins and export syntax are explained before use. PostgreSQL, original synthetic ticket data and Terminal are prepared; no Python or frontend development is required.
Create or edit windows.sql, daily-close.sql and the requested close-YYYY-MM-DD.csv reports in /home/labex/project/day-close. Preserve the original tables, venue IDs/names/zones, ticket IDs/instants/amounts/staff notes and supplied tools. Free tickets still count as tickets, and separate receipts with equal prices or clock readings remain separate. This is a simplified ticket-sales closing exercise, without taxes, refunds, payment-provider settlement or accounting guarantees.
All source instants have explicit UTC/offset information. We use fixed historical dates and known local midnights; parsing ambiguous clock readings without an offset is outside this exercise. Runtime labels and outputs remain English.
Build the Right Local-Day Windows
In this step, you will turn each venue's requested calendar date into two real boundary instants, without assuming that a local day lasts 24 hours.
Start in Terminal at the normal shell. ./ runs a supplied program in the current directory. The db tool opens a read-only PostgreSQL connection; desk will run your saved queries and display their real results. First inspect the two receipts in New York time and read the venue settings:
A here-document sends the lines between the quoted SQL marker and the closing SQL to db. The closing marker must be alone on its line. SQL statements end with semicolons. This connection closes when the block ends; its SET TIME ZONE affects only this connection. The next ./db starts a fresh connection in UTC. WHERE ... IN (...) keeps the listed ticket IDs, and ORDER BY orders the display 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
The receipts show 2024-11-03 01:30:00-04 and 2024-11-03 01:30:00-05. The offsets mean four and five hours behind UTC, the common time reference. The clock reads the same, but the second instant is an hour later. Keep both IDs, even though both tickets cost 500 cents.
sold_at has PostgreSQL type timestamptz, short for timestamp with time zone. It represents an instant and displays it in the connection's TimeZone. It does not preserve the original time-zone name or original written offset. Each venue therefore has a separate IANA zone name, such as America/New_York, whose date-specific rules determine its offset. A fixed offset is not a substitute for that name.
Read the same stored instants in a fresh UTC connection. Here -c runs the complete quoted SQL string and exits:
./db -c "SELECT ticket_id, sold_at FROM tickets WHERE ticket_id IN ('FOLD-1','FOLD-2') ORDER BY ticket_id;"
The values are 05:30 and 06:30 with the +00 offset. Changing a display zone did not change the tickets.
A date is a calendar label. A timestamp without time zone is a clock reading, not yet a globally identified instant. PostgreSQL's ::type converts a value to a type. Converting a date to timestamp gives that date at 00:00. Applied to this local timestamp, AT TIME ZONE interprets it using the named venue zone and returns a timestamptz instant. Date plus integer 1 means the next calendar date.
For Harbor's November 3 sales day, convert each midnight separately:
./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
The UTC opening is November 3 at 04:00 and the UTC closing is November 4 at 05:00: 25 elapsed hours. The spring change on March 10 has only 23 elapsed hours. Adding a fixed 24 hours to the opening would give the wrong closing instant on both days. Zone offsets need not be whole hours, and some clock changes are only half an hour; use the actual rules at both midnights rather than hardcoding an offset or duration.
Save a reusable query for all venues. WITH windows AS (...) gives an intermediate query a name, called a common table expression. v is a short table alias for venues. AS gives a calculated column its output name. This keeps local calendar work separate from display formatting.
:'trading_date' is a psql variable supplied by the desk for each requested date. The colon requests substitution and the single quotes make psql quote the value as an SQL string. Convert that value to date instead of fixing one date into the query.
In the final SELECT, AT TIME ZONE 'UTC' works in the other direction: it converts a timestamptz instant to its UTC clock representation. to_char formats that representation. YYYY-MM-DD is year/month/day, HH24 is a 24-hour clock, MI means minutes and SS means seconds. Double-quoted T and Z in the format are literal labels. Z means UTC, so convert to UTC before adding that label; printing Z alone does not convert anything.
Subtracting the instants gives an elapsed interval. EXTRACT(EPOCH FROM ...) reads its total seconds; dividing by 3600 gives elapsed hours, including a fractional hour if appropriate. Reading only the hour component would not give the full duration.
cat > windows.sql writes the complete query file below. The quoted SQL marker prevents the shell from expanding the body. Paste the whole block, retaining the closing marker on its own line:
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
Use the desk to supply different trading dates to the same file. --zone changes the connection display zone for this invocation, not the venue date:
./desk windows 2024-11-03
./desk windows 2024-03-10
./desk windows 2024-11-03 --zone Asia/Tokyo
The output columns are venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc and hours, in that order. Harbor's values are 25 and 23 hours; Berlin has 24 on these dates. --zone changes the database connection's display zone, not the requested venue date. Explicit UTC boundaries stay the same.
If you mistype a filename or command, read the error, use ls to inspect the actual files and retry the corrected command. A Day close error is an execution error, not a successful no-sales report. To repair a query, correct the saved SQL and rerun it; original ticket data does not need to change. Run the step check after reading the actual windows.
Keep Every Ticket Inside Its Venue's Day
In this step, you will use the local-day boundaries to select every eligible original ticket exactly once while retaining venues that had no sales.
Stay in Terminal and /home/labex/project/day-close. Keep windows.sql. A day window is half-open: include its opening instant and exclude its closing instant. A receipt at the next midnight belongs to the next day. The SQL comparisons are >= for greater than or equal and < for strictly less. BETWEEN includes both endpoints, so it would count a receipt at closing in two adjacent days.
For the Harbor window you just observed, inspect the actual tickets using explicit UTC boundary literals. Here -c runs the quoted SQL and exits; each literal's +00 makes its UTC meaning explicit:
./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 tickets belong to this local day. Both clock-repeat receipts remain present; the free admission has amount_cents equal to 0. A zero amount does not mean there was no ticket. The NEXT receipt at the closing midnight is outside this day. The LATE receipt just before it is inside, including its microseconds. Do not approximate the exclusive end by subtracting one second.
Extend the window CTE into a reusable ledger query. A join associates rows from two sources. w refers to one window row and t to a ticket. LEFT JOIN keeps a window even if no ticket meets its ON conditions. Its ticket columns are then NULL, meaning no matching value. Put the time conditions in ON; moving them to a final WHERE would remove the empty venue. The venue-ID condition prevents a venue from receiving another venue's receipts.
Each real ticket produces one row with its unchanged ID, UTC instant and integer cents. An empty venue produces one row with blank ticket ID, sale instant and amount in the CSV representation. This is an empty-venue row, not a zero-price ticket. Keep venue identity, name, zone and date on every row.
The output columns, in order, are venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc, ticket_id, sold_utc, amount_cents. UTC fields must explicitly identify UTC, with Z or an equivalent zero offset. A real ticket's full instant must survive; US in the formatting pattern preserves six microsecond digits. Null sale instants remain null through to_char. Result row order may differ, but every required row and value must remain.
Write the complete saved query. It uses the same date parameter and two-midnight conversions you already practiced:
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
The desk's close action runs the saved SQL unchanged and displays its actual rows plus a summary derived only from those returned rows. It does not add missing tickets, choose a correct boundary or correct a wrong price. The summary counts a real ticket ID even when its amount is 0. Ask for the autumn, spring and a no-sales date:
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
For November 3, Harbor has six tickets totaling 4750 cents; Berlin has four totaling 3750. For March 10, Harbor has four totaling 3750, and Berlin still appears with zero tickets and zero cents. July 1 has an empty-venue row for each venue and both summaries are zero. An SQL error is different from these valid no-sales rows.
Same clock values, equal prices and duplicate venue names are not identity rules. Keep original ticket and venue IDs; do not collapse different receipts with DISTINCT or replace the ledger with a total alone. Run the step check after inspecting the rows as well as the totals.
Deliver Reports That Keep Their Meaning
In this step, you will export the actual closing-query rows into two real CSV files and confirm that a connection's display zone does not move a sale into a different venue day.
Stay in Terminal and the day-close directory. Keep both SQL files. --zone chooses the PostgreSQL connection's display TimeZone for that one desk invocation. It does not change the venue's stored zone, its trading date or the source tickets. Compare the same closed day under two other connection zones:
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
Both outputs still contain the same original tickets, venue dates, explicit UTC windows and totals. If a query uses a bare cast of sold_at to date, or formats a local clock and labels it Z, changing the connection zone can expose the mistake. Correct the query rather than changing global database settings or rewriting source times.
CSV stores a header and delimited rows; quoting protects values that contain commas. The desk's export action writes the native PostgreSQL CSV output to close-YYYY-MM-DD.csv in this workspace. It replaces only that date's report if the file already exists. No reference calculation repairs the query before writing. The read action parses only the saved file and derives its displayed summary from those actual rows; it makes no database query and never fills omitted rows from the source.
Export both documented dates, then read their actual files. You can keep and reopen these reports when the source database is unavailable:
./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
The two files retain the documented nine-column ledger. Their counts and totals agree with the real query results you inspected: 6/4750 and 4/3750 in autumn, then 4/3750 and 0/0 in spring for Harbor/Berlin. Both IDs for the repeated 01:30 stay in the autumn file, and the free ticket remains a real row. A screenshot or a hand-typed total would not preserve these facts.
Keep windows.sql, daily-close.sql, both requested reports and the original database/tools. A query correction does not silently update an older file: explicitly export that date again, then read the saved file to check it. If you mistype a report filename, use ls and retry the actual name rather than changing the database. Run the final check after confirming the saved rows.
Summary
You separated calendar labels, clock readings, instants and IANA zone rules. You converted both local midnights independently, used a half-open window to preserve every original ticket once, retained empty venues and exported real reports with explicit UTC instants. Your saved queries work across session TimeZone changes and your file reader displays only the actual saved rows. These are simplified historical ticket closings, not payment settlement or arbitrary ambiguous-time parsing.
