Введение
Передвижная выставка продаёт билеты в Harbor Tickets в Нью-Йорке и Berlin Pop-Up в Берлине. Каждая площадка закрывает день продаж в свою местную полночь. В день перевода часов такие местные сутки могут длиться 23 или 25 часов. Две квитанции Harbor показывают 01:30, однако разные смещения относительно UTC обозначают два разных фактических момента времени и два разных билета.
Вы сохраните запросы PostgreSQL для повторного использования, рассчитывающие интервалы местных суток и полные реестры билетов, а затем подготовите настоящие CSV-отчёты закрытия. Нужны базовые навыки навигации в терминале, подключения к PostgreSQL и знание SELECT/WHERE. Типы даты и времени, именованные часовые пояса, шаблоны запросов, соединения и синтаксис экспорта объясняются перед использованием. PostgreSQL, исходные синтетические данные билетов и Terminal уже подготовлены; разработка на Python или создание интерфейса не требуются.
Создайте или отредактируйте windows.sql, daily-close.sql и запрошенные отчёты close-YYYY-MM-DD.csv в /home/labex/project/day-close. Сохраните исходные таблицы, ID, названия и часовые пояса площадок, ID, моменты времени, суммы и заметки сотрудников для билетов, а также предоставленные инструменты. Бесплатные билеты всё равно считаются билетами, а отдельные квитанции с одинаковыми ценами или показаниями часов остаются отдельными. Это упрощённое упражнение по закрытию продаж билетов без налогов, возвратов, расчётов с платёжным провайдером или бухгалтерских гарантий.
Все исходные моменты времени содержат явную информацию UTC или смещения. Мы используем фиксированные исторические даты и известные местные полуночи; разбор неоднозначных показаний часов без смещения выходит за рамки упражнения. Метки и вывод работающих инструментов остаются на английском.
Постройте правильные интервалы местных суток
На этом шаге вы превратите запрошенную календарную дату каждой площадки в два настоящих граничных момента времени, не предполагая, что местные сутки длятся 24 часа.
Начните во вкладке Terminal с обычной командной оболочки. ./ запускает предоставленную программу в текущем каталоге. Инструмент db открывает соединение PostgreSQL только для чтения; desk будет запускать ваши сохранённые запросы и показывать их настоящие результаты. Сначала изучите две квитанции во времени Нью-Йорка и прочитайте настройки площадок:
Here-document отправляет строки между маркером 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, общей точки отсчёта времени. Часы показывают одно и то же, но второй момент наступил на час позже. Сохраните оба ID, хотя оба билета стоят 500 центов.
sold_at имеет тип PostgreSQL timestamptz, сокращение от timestamp with time zone. Он представляет момент времени и показывает его в TimeZone соединения. Он не сохраняет исходное название часового пояса или первоначально записанное смещение. Поэтому у каждой площадки есть отдельное имя часового пояса IANA, например America/New_York, правила которого для конкретной даты определяют смещение. Фиксированное смещение не заменяет это имя.
Прочитайте те же сохранённые моменты в новом соединении UTC. Здесь -c выполняет полную строку SQL в кавычках и завершает работу:
./db -c "SELECT ticket_id, sold_at FROM tickets WHERE ticket_id IN ('FOLD-1','FOLD-2') ORDER BY ticket_id;"
Значения — 05:30 и 06:30 со смещением +00. Изменение часового пояса отображения не изменило билеты.
date — календарная дата. timestamp without time zone — показание часов, ещё не определённое как момент на общей временной шкале. Синтаксис PostgreSQL ::type преобразует значение к типу. Преобразование даты в timestamp даёт эту дату в 00:00. Применённый к такому локальному timestamp оператор AT TIME ZONE интерпретирует его с использованием именованного часового пояса площадки и возвращает момент timestamptz. Дата плюс целое число 1 означает следующую календарную дату.
Для дня продаж Harbor 3 ноября преобразуйте каждую полночь отдельно:
./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 — 3 ноября в 04:00, а конец по UTC — 4 ноября в 05:00: 25 прошедших часов. При весеннем переводе 10 марта проходят только 23 часа. Прибавление фиксированных 24 часов к началу дало бы неправильный конечный момент в обоих случаях. Смещения часовых поясов не обязательно составляют целое число часов, а некоторые переводы часов происходят лишь на полчаса; используйте настоящие правила в обе полуночи, а не жёстко заданные смещение или длительность.
Сохраните запрос для повторного использования для всех площадок. WITH windows AS (...) даёт промежуточному запросу имя; это обобщённое табличное выражение. v — короткий псевдоним таблицы venues. AS даёт вычисляемому столбцу имя в результате. Это отделяет работу с местным календарём от форматирования отображения.
:'trading_date' — переменная psql, передаваемая программой для каждой запрошенной даты. Двоеточие запрашивает подстановку, а одинарные кавычки заставляют psql заключить значение в кавычки как строку SQL. Преобразуйте это значение к дате вместо закрепления одной даты в запросе.
В финальном SELECT AT TIME ZONE 'UTC' работает в другом направлении: преобразует момент timestamptz в его показание часов по UTC. to_char форматирует это представление. YYYY-MM-DD — год, месяц и день, HH24 — 24-часовой формат, MI — минуты, SS — секунды. T и Z в двойных кавычках внутри формата — буквальные метки. Z означает UTC, поэтому преобразуйте время в UTC перед добавлением этой метки; один лишь вывод Z ничего не преобразует.
Вычитание моментов даёт прошедший интервал. 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
Используйте программу, чтобы передавать разные даты продаж одному и тому же файлу. --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 и запустите снова; исходные данные билетов менять не нужно. Запустите проверку шага после чтения настоящих интервалов.
Сохраните каждый билет в пределах дня его площадки
На этом шаге вы используете границы местных суток, чтобы выбрать каждый подходящий исходный билет ровно один раз, сохранив площадки, где не было продаж.
Оставайтесь во вкладке Terminal и в /home/labex/project/day-close. Сохраните windows.sql. Интервал дня полуоткрытый: включайте начальный момент и исключайте конечный. Квитанция в следующую полночь относится к следующему дню. Сравнения 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;"
К этим местным суткам относятся шесть билетов. Обе квитанции с повторившимся показанием часов остаются; у бесплатного входа amount_cents равно 0. Нулевая сумма не означает отсутствия билета. Квитанция NEXT в конечную полночь находится вне этого дня. Квитанция LATE непосредственно перед ней входит в него, включая её микросекунды. Не заменяйте исключённую конечную границу приближением с вычитанием одной секунды.
Расширьте CTE интервалов до запроса реестра для повторного использования. Соединение связывает строки из двух источников. w относится к одной строке интервала, а t — к билету. LEFT JOIN сохраняет интервал, даже если ни один билет не удовлетворяет его условиям ON. Тогда столбцы билета имеют значение NULL, означающее отсутствие подходящего значения. Поместите условия времени в ON; перенос их в итоговый WHERE убрал бы площадку без продаж. Условие по ID площадки предотвращает попадание квитанций одной площадки к другой.
Каждый настоящий билет даёт одну строку с неизменными ID, моментом UTC и целым числом центов. Площадка без продаж даёт одну строку с пустыми ID билета, моментом продажи и суммой в представлении CSV. Это строка площадки без продаж, а не билет с нулевой ценой. Сохраняйте идентификатор площадки, название, часовой пояс и дату в каждой строке.
Выходные столбцы по порядку — venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc, ticket_id, sold_utc, amount_cents. Поля UTC должны явно обозначать UTC, с помощью Z или эквивалентного нулевого смещения. Полный момент настоящего билета должен сохраниться; US в шаблоне форматирования сохраняет шесть цифр микросекунд. Отсутствующие моменты продаж остаются null при прохождении через to_char. Порядок строк результата может отличаться, но все требуемые строки и значения должны сохраниться.
Запишите полный сохранённый запрос. Он использует тот же параметр даты и преобразования двух полуночей, которые вы уже отработали:
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
Действие close программы выполняет сохранённый SQL без изменений и показывает фактические строки плюс сводку, вычисленную только по этим возвращённым строкам. Оно не добавляет пропущенные билеты, не выбирает правильную границу и не исправляет неверную цену. Сводка считает настоящий ID билета, даже когда его сумма равна 0. Запросите осеннюю, весеннюю дату и дату без продаж:
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
Для 3 ноября у Harbor шесть билетов на 4750 центов; у Berlin четыре на 3750. Для 10 марта у Harbor четыре на 3750, а Berlin по-прежнему отображается с нулём билетов и нулём центов. Для 1 июля есть строка площадки без продаж для каждой площадки, и обе сводки нулевые. Ошибка SQL отличается от этих допустимых строк без продаж.
Одинаковые показания часов, равные цены и повторяющиеся названия площадок не являются правилами идентификации. Сохраняйте исходные ID билетов и площадок; не объединяйте разные квитанции с помощью DISTINCT и не заменяйте реестр одним итогом. Запустите проверку шага после изучения и строк, и итогов.
Подготовьте отчёты, сохраняющие свой смысл
На этом шаге вы экспортируете фактические строки запроса закрытия в два настоящих CSV-файла и убедитесь, что часовой пояс отображения соединения не переносит продажу в другой день площадки.
Оставайтесь во вкладке Terminal и в каталоге day-close. Сохраните оба файла SQL. --zone выбирает TimeZone отображения соединения PostgreSQL для одного вызова программы. Он не меняет сохранённый часовой пояс площадки, её дату продаж или исходные билеты. Сравните тот же закрытый день в двух других часовых поясах соединения:
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
Оба вывода по-прежнему содержат те же исходные билеты, даты площадок, явные интервалы UTC и итоги. Если запрос использует простое приведение sold_at к дате или форматирует местное показание часов и помечает его Z, изменение часового пояса соединения может выявить ошибку. Исправьте запрос вместо изменения глобальных настроек базы данных или переписывания исходного времени.
CSV хранит заголовок и строки с разделителями; кавычки защищают значения с запятыми. Действие export программы записывает собственный CSV-вывод PostgreSQL в 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
Два файла сохраняют описанный реестр с девятью столбцами. Их количества и итоги совпадают с настоящими результатами запросов, которые вы изучили: 6/4750 и 4/3750 осенью, затем 4/3750 и 0/0 весной для Harbor/Berlin. Оба ID для повторившегося 01:30 остаются в осеннем файле, а бесплатный билет остаётся настоящей строкой. Снимок экрана или набранный вручную итог не сохранили бы эти факты.
Сохраните windows.sql, daily-close.sql, оба запрошенных отчёта и исходную базу данных и инструменты. Исправление запроса не обновляет старый файл автоматически: явно экспортируйте эту дату заново, затем прочитайте сохранённый файл для проверки. При опечатке в имени отчёта используйте ls и повторите с фактическим именем вместо изменения базы данных. Запустите итоговую проверку после подтверждения сохранённых строк.
Резюме
Вы разделили календарные даты, показания часов, моменты времени и правила часовых поясов IANA. Вы независимо преобразовали обе местные полуночи, использовали полуоткрытый интервал, чтобы сохранить каждый исходный билет один раз, сохранили площадки без продаж и экспортировали настоящие отчёты с явно указанными моментами UTC. Сохранённые запросы работают при изменении TimeZone сеанса, а средство чтения файлов показывает только фактически сохранённые строки. Это упрощённое историческое закрытие продаж билетов, а не расчёты по платежам или разбор произвольного неоднозначного времени.
