Introdução
A exposição itinerante vende ingressos em Harbor Tickets, em Nova York, e Berlin Pop-Up, em Berlim. Cada espaço encerra um dia de vendas à sua própria meia-noite local. Em um dia de mudança de horário, esse dia local pode durar 23 ou 25 horas. Dois comprovantes de Harbor mostram 01:30, mas seus diferentes deslocamentos em relação ao UTC identificam dois instantes reais diferentes e dois ingressos diferentes.
Você salvará consultas reutilizáveis do PostgreSQL para os intervalos de dias locais e registros completos de ingressos e entregará relatórios reais de fechamento em CSV. Você precisa saber navegar pelo terminal e conhecer conexões PostgreSQL e SELECT/WHERE. Tipos de data/hora, fusos nomeados, modelos de consulta, junções e sintaxe de exportação são explicados antes do uso. O PostgreSQL, os dados sintéticos originais de ingressos e Terminal estão preparados; não é necessário desenvolver em Python nem criar um frontend.
Crie ou edite windows.sql, daily-close.sql e os relatórios solicitados close-YYYY-MM-DD.csv em /home/labex/project/day-close. Preserve as tabelas originais, os IDs/nomes/fusos dos espaços, os IDs/instantes/valores/notas da equipe dos ingressos e as ferramentas fornecidas. Ingressos gratuitos continuam contando como ingressos, e comprovantes separados com preços ou horários de relógio iguais continuam separados. Este é um exercício simplificado de fechamento de vendas de ingressos, sem impostos, reembolsos, liquidação por provedores de pagamento nem garantias contábeis.
Todos os instantes de origem têm informação explícita de UTC/deslocamento. Usamos datas históricas fixas e meias-noites locais conhecidas; interpretar horários de relógio ambíguos sem um deslocamento está fora deste exercício. Os rótulos e as saídas do ambiente de execução permanecem em inglês.
Criar os intervalos corretos do dia local
Nesta etapa, você transformará a data de calendário solicitada de cada espaço em dois instantes reais de limite, sem presumir que um dia local dura 24 horas.
Comece em Terminal, no shell normal. ./ executa um programa fornecido no diretório atual. A ferramenta db abre uma conexão PostgreSQL somente leitura; desk executará suas consultas salvas e exibirá seus resultados reais. Primeiro, inspecione os dois comprovantes no horário de Nova York e consulte as configurações dos espaços:
Um here-document envia as linhas entre o marcador SQL entre aspas e o SQL de fechamento para db. O marcador de fechamento deve estar sozinho em sua linha. Instruções SQL terminam com ponto e vírgula. Esta conexão fecha quando o bloco termina; seu SET TIME ZONE afeta apenas esta conexão. O próximo ./db inicia uma nova conexão em UTC. WHERE ... IN (...) mantém os IDs de ingresso listados, e ORDER BY ordena a exibição por 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
Os comprovantes mostram 2024-11-03 01:30:00-04 e 2024-11-03 01:30:00-05. Os deslocamentos significam quatro e cinco horas atrás do UTC, a referência comum de tempo. O relógio marca o mesmo horário, mas o segundo instante é uma hora depois. Preserve os dois IDs, embora os dois ingressos custem 500 centavos.
sold_at tem o tipo timestamptz do PostgreSQL, abreviação de timestamp with time zone. Ele representa um instante e o exibe no TimeZone da conexão. Não preserva o nome original do fuso horário nem o deslocamento escrito originalmente. Portanto, cada espaço tem um nome de fuso IANA separado, como America/New_York, cujas regras específicas de cada data determinam seu deslocamento. Um deslocamento fixo não substitui esse nome.
Consulte os mesmos instantes armazenados em uma nova conexão UTC. Aqui, -c executa a string SQL completa entre aspas e sai:
./db -c "SELECT ticket_id, sold_at FROM tickets WHERE ticket_id IN ('FOLD-1','FOLD-2') ORDER BY ticket_id;"
Os valores são 05:30 e 06:30 com o deslocamento +00. Alterar um fuso de exibição não alterou os ingressos.
Um valor date é um rótulo de calendário. Um timestamp without time zone é uma leitura de relógio, ainda não um instante identificado globalmente. O ::type do PostgreSQL converte um valor em um tipo. Converter uma data em timestamp produz essa data às 00:00. Aplicado a esse timestamp local, AT TIME ZONE o interpreta usando o fuso nomeado do espaço e retorna um instante timestamptz. Data mais o inteiro 1 significa a próxima data de calendário.
Para o dia de vendas de 3 de novembro de Harbor, converta cada meia-noite separadamente:
./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
A abertura UTC é em 3 de novembro às 04:00, e o fechamento UTC é em 4 de novembro às 05:00: 25 horas decorridas. A mudança da primavera em 10 de março tem apenas 23 horas decorridas. Somar 24 horas fixas à abertura produziria o instante de fechamento errado nos dois dias. Os deslocamentos dos fusos não precisam ser horas inteiras, e algumas mudanças de horário são de apenas meia hora; use as regras reais nas duas meias-noites em vez de fixar um deslocamento ou uma duração no código.
Salve uma consulta reutilizável para todos os espaços. WITH windows AS (...) dá um nome a uma consulta intermediária, chamada expressão de tabela comum. v é um alias curto de tabela para venues. AS dá a uma coluna calculada seu nome de saída. Isso mantém o trabalho de calendário local separado da formatação de exibição.
:'trading_date' é uma variável psql fornecida pelo posto para cada data solicitada. Os dois-pontos solicitam substituição, e as aspas simples fazem o psql colocar o valor entre aspas como uma string SQL. Converta esse valor em date em vez de fixar uma data na consulta.
No SELECT final, AT TIME ZONE 'UTC' funciona no sentido inverso: converte um instante timestamptz em sua representação de relógio UTC. to_char formata essa representação. YYYY-MM-DD é ano/mês/dia, HH24 é um relógio de 24 horas, MI significa minutos, e SS significa segundos. T e Z entre aspas duplas no formato são rótulos literais. Z significa UTC, portanto converta para UTC antes de adicionar esse rótulo; apenas imprimir Z não converte nada.
Subtrair os instantes produz um intervalo decorrido. EXTRACT(EPOCH FROM ...) lê seu total de segundos; dividir por 3600 produz as horas decorridas, incluindo uma fração de hora quando apropriado. Ler apenas o componente de horas não forneceria a duração completa.
cat > windows.sql grava o arquivo completo de consulta abaixo. O marcador SQL entre aspas impede o shell de expandir o conteúdo. Cole o bloco inteiro, mantendo o marcador de fechamento sozinho em sua linha:
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 o posto para fornecer datas de vendas diferentes ao mesmo arquivo. --zone altera o fuso de exibição da conexão para esta execução, não a data do espaço:
./desk windows 2024-11-03
./desk windows 2024-03-10
./desk windows 2024-11-03 --zone Asia/Tokyo
As colunas de saída são venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc e hours, nessa ordem. Os valores de Harbor são 25 e 23 horas; Berlin tem 24 nessas datas. --zone altera o fuso de exibição da conexão do banco de dados, não a data solicitada do espaço. Os limites UTC explícitos permanecem iguais.
Se digitar um nome de arquivo ou comando incorretamente, leia o erro, use ls para inspecionar os arquivos reais e tente novamente com o comando corrigido. Um Day close error é um erro de execução, não um relatório bem-sucedido de ausência de vendas. Para corrigir uma consulta, corrija o SQL salvo e execute-o novamente; os dados originais dos ingressos não precisam mudar. Execute a verificação da etapa após consultar os intervalos reais.
Manter cada ingresso dentro do dia de seu espaço
Nesta etapa, você usará os limites do dia local para selecionar cada ingresso original elegível exatamente uma vez, mantendo espaços que não tiveram vendas.
Permaneça em Terminal e em /home/labex/project/day-close. Mantenha windows.sql. Um intervalo de dia é semiaberto: inclui seu instante de abertura e exclui seu instante de fechamento. Um comprovante na próxima meia-noite pertence ao próximo dia. As comparações SQL são >= para maior ou igual e < para estritamente menor. BETWEEN inclui os dois extremos, portanto contaria um comprovante no fechamento em dois dias adjacentes.
Para o intervalo de Harbor que você acabou de observar, inspecione os ingressos reais usando literais explícitos de limite UTC. Aqui, -c executa o SQL entre aspas e sai; o +00 de cada literal torna explícito seu significado 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;"
Seis ingressos pertencem a esse dia local. Os dois comprovantes de horário repetido continuam presentes; o ingresso gratuito tem amount_cents igual a 0. Um valor zero não significa que não houve ingresso. O comprovante NEXT na meia-noite de fechamento está fora desse dia. O comprovante LATE logo antes dele está dentro, incluindo seus microssegundos. Não aproxime o fim exclusivo subtraindo um segundo.
Amplie a CTE do intervalo para uma consulta reutilizável de registros. Uma junção associa linhas de duas origens. w se refere a uma linha de intervalo, e t, a um ingresso. LEFT JOIN mantém um intervalo mesmo que nenhum ingresso atenda às suas condições ON. Suas colunas de ingresso são então NULL, que significa ausência de valor correspondente. Coloque as condições de tempo em ON; movê-las para um WHERE final removeria o espaço sem vendas. A condição de ID do espaço impede que um espaço receba comprovantes de outro.
Cada ingresso real produz uma linha com seu ID inalterado, instante UTC e centavos inteiros. Um espaço sem vendas produz uma linha com ID de ingresso, instante da venda e valor em branco na representação CSV. Essa é uma linha de espaço sem vendas, não um ingresso de preço zero. Mantenha a identidade, o nome, o fuso e a data do espaço em cada linha.
As colunas de saída, na ordem, são venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc, ticket_id, sold_utc, amount_cents. Os campos UTC devem identificar explicitamente UTC, com Z ou um deslocamento zero equivalente. O instante completo de um ingresso real deve ser preservado; US no padrão de formatação mantém seis dígitos de microssegundos. Instantes de venda null continuam null ao passar por to_char. A ordem das linhas de resultado pode variar, mas cada linha e valor exigidos devem permanecer.
Grave a consulta salva completa. Ela usa o mesmo parâmetro de data e as conversões das duas meias-noites que você já praticou:
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
A ação close do posto executa o SQL salvo sem alterações e exibe suas linhas reais, além de um resumo derivado apenas dessas linhas retornadas. Ela não adiciona ingressos ausentes, não escolhe um limite correto nem corrige um preço errado. O resumo conta um ID de ingresso real mesmo quando seu valor é 0. Solicite as datas de outono, primavera e uma data sem vendas:
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
Em 3 de novembro, Harbor tem seis ingressos totalizando 4750 centavos; Berlin tem quatro totalizando 3750. Em 10 de março, Harbor tem quatro totalizando 3750, e Berlin continua aparecendo com zero ingressos e zero centavos. Em 1º de julho há uma linha de espaço sem vendas para cada espaço, e os dois resumos são zero. Um erro SQL é diferente dessas linhas válidas de ausência de vendas.
Horários de relógio iguais, preços iguais e nomes de espaços duplicados não são regras de identidade. Preserve os IDs originais de ingressos e espaços; não una comprovantes diferentes com DISTINCT nem substitua os registros apenas por um total. Execute a verificação da etapa após inspecionar tanto as linhas quanto os totais.
Entregar relatórios que preservem seu significado
Nesta etapa, você exportará as linhas reais da consulta de fechamento para dois arquivos CSV reais e confirmará que o fuso de exibição de uma conexão não move uma venda para um dia diferente do espaço.
Permaneça em Terminal e no diretório day-close. Mantenha os dois arquivos SQL. --zone escolhe o TimeZone de exibição da conexão PostgreSQL para essa única execução do posto. Não altera o fuso armazenado do espaço, sua data de vendas nem os ingressos de origem. Compare o mesmo dia encerrado sob outros dois fusos de conexão:
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
As duas saídas ainda contêm os mesmos ingressos originais, datas dos espaços, intervalos UTC explícitos e totais. Se uma consulta usa uma conversão direta de sold_at para date ou formata um horário de relógio local e o rotula com Z, mudar o fuso da conexão pode revelar o erro. Corrija a consulta em vez de alterar configurações globais do banco de dados ou reescrever horários de origem.
CSV armazena um cabeçalho e linhas delimitadas; as aspas protegem valores que contêm vírgulas. A ação export do posto grava a saída CSV nativa do PostgreSQL em close-YYYY-MM-DD.csv neste espaço de trabalho. Ela substitui apenas o relatório dessa data se o arquivo já existir. Nenhum cálculo de referência corrige a consulta antes da gravação. A ação read interpreta apenas o arquivo salvo e deriva seu resumo exibido dessas linhas reais; não faz consulta ao banco de dados e nunca preenche linhas omitidas a partir da origem.
Exporte as duas datas documentadas e consulte seus arquivos reais. Você pode manter e reabrir esses relatórios quando o banco de dados de origem estiver indisponível:
./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
Os dois arquivos mantêm os registros documentados de nove colunas. Suas contagens e totais correspondem aos resultados reais da consulta que você inspecionou: 6/4750 e 4/3750 no outono, depois 4/3750 e 0/0 na primavera para Harbor/Berlin. Os dois IDs do horário repetido 01:30 permanecem no arquivo de outono, e o ingresso gratuito continua sendo uma linha real. Uma captura de tela ou um total digitado manualmente não preservaria esses fatos.
Mantenha windows.sql, daily-close.sql, os dois relatórios solicitados e o banco de dados/as ferramentas originais. Uma correção de consulta não atualiza silenciosamente um arquivo antigo: exporte explicitamente essa data novamente e leia o arquivo salvo para verificá-lo. Se digitar um nome de arquivo de relatório incorretamente, use ls e tente novamente com o nome real, em vez de alterar o banco de dados. Execute a verificação final após confirmar as linhas salvas.
Resumo
Você separou rótulos de calendário, leituras de relógio, instantes e regras de fusos IANA. Converteu as duas meias-noites locais independentemente, usou um intervalo semiaberto para preservar cada ingresso original uma vez, manteve espaços sem vendas e exportou relatórios reais com instantes UTC explícitos. Suas consultas salvas funcionam com mudanças de TimeZone da sessão, e seu leitor de arquivos exibe apenas as linhas realmente salvas. Estes são fechamentos históricos simplificados de ingressos, não liquidação de pagamentos nem interpretação arbitrária de horários ambíguos.
