소개
순회 전시는 뉴욕의 Harbor Tickets와 베를린의 Berlin Pop-Up에서 티켓을 판매합니다. 각 장소는 자체 현지 자정에 하루 판매를 마감합니다. 시계가 바뀌는 날에는 현지 하루가 23시간이나 25시간일 수 있습니다. Harbor 영수증 두 장에는 모두 01:30이 표시되지만, 서로 다른 UTC 오프셋으로 실제 시점이 다르고 서로 다른 티켓임을 알 수 있습니다.
현지 하루 구간과 완전한 티켓 원장을 위한 재사용 가능한 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/오프셋 정보가 있습니다. 고정된 과거 날짜와 알려진 현지 자정을 사용합니다. 오프셋 없는 모호한 시계 표시를 해석하는 것은 이 실습의 범위 밖입니다. 실행 환경의 표시 이름과 출력은 영어로 유지됩니다.
올바른 현지 하루 구간 만들기
이 단계에서는 현지 하루가 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보다 각각 4시간, 5시간 늦음을 뜻합니다. 시계 표시는 같지만 두 번째 시점은 한 시간 뒤입니다. 두 티켓 모두 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일 판매일에 대해 각 자정을 따로 변환하세요:
./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이고 UTC 마감은 11월 4일 05:00으로, 경과 시간은 25시간입니다. 봄에 시계가 바뀌는 3월 10일은 경과 시간이 23시간뿐입니다. 시작에 고정된 24시간을 더하면 두 날 모두 마감 시점이 잘못됩니다. 시간대 오프셋이 꼭 정수 시간인 것은 아니고 일부 시계 변경은 30분뿐입니다. 오프셋이나 기간을 고정해 작성하지 말고 두 자정에서 실제 규칙을 사용하세요.
모든 장소에 재사용할 쿼리를 저장하세요. WITH windows AS (...)는 중간 쿼리에 이름을 붙이며 이를 공통 테이블 표현식이라고 합니다. v는 venues의 짧은 테이블 별칭입니다. AS는 계산된 열에 출력 이름을 붙입니다. 이 구조로 현지 달력 계산과 표시 형식을 구분합니다.
:'trading_date'는 요청한 날짜마다 데스크가 제공하는 psql 변수입니다. 콜론은 치환을 요청하고 작은따옴표는 psql이 값을 SQL 문자열로 따옴표 처리하게 합니다. 날짜 하나를 쿼리에 고정하지 말고 이 값을 date로 변환하세요.
최종 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시간이며, 베를린은 이 날짜들에 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입니다. 금액이 0이라고 티켓이 없었다는 뜻은 아닙니다. 마감 자정의 NEXT 영수증은 이 날의 범위 밖입니다. 그 직전의 LATE 영수증은 마이크로초까지 포함해 범위 안입니다. 제외할 끝점을 1초 빼는 방식으로 근사하지 마세요.
구간 CTE를 재사용 가능한 원장 쿼리로 확장하세요. 조인은 두 원본의 행을 연결합니다. w는 구간 행 하나를, t는 티켓을 가리킵니다. LEFT JOIN은 ON 조건을 만족하는 티켓이 없어도 구간을 유지합니다. 이때 티켓 열은 일치하는 값이 없음을 뜻하는 NULL입니다. 시간 조건은 ON에 두세요. 최종 WHERE로 옮기면 판매가 없는 장소가 사라집니다. 장소 ID 조건은 다른 장소의 영수증이 배정되지 않도록 합니다.
각 실제 티켓은 변경되지 않은 ID, UTC 시점, 정수 센트를 가진 행 하나를 만듭니다. 판매가 없는 장소는 CSV 표현에서 티켓 ID, 판매 시점, 금액이 빈 행 하나를 만듭니다. 이는 판매 없는 장소의 행이며 가격이 0인 티켓이 아닙니다. 모든 행에 장소 식별 정보, 이름, 시간대, 날짜를 유지하세요.
출력 열은 순서대로 venue_id, venue_name, time_zone, trading_date, opens_utc, closes_utc, ticket_id, sold_utc, amount_cents입니다. UTC 필드는 Z 또는 동등한 0 오프셋으로 UTC임을 명시해야 합니다. 실제 티켓의 시점 전체가 보존되어야 합니다. 형식 패턴의 US는 마이크로초 여섯 자리를 보존합니다. null 판매 시점은 to_char를 거쳐도 null로 남습니다. 결과 행 순서는 달라도 되지만 필요한 모든 행과 값은 유지되어야 합니다.
전체 쿼리를 파일에 쓰세요. 이미 연습한 것과 같은 날짜 매개변수와 두 자정 변환을 사용합니다:
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을 변경 없이 실행하고 실제 행 및 그 반환 행만으로 도출한 요약을 표시합니다. 빠진 티켓을 추가하거나, 올바른 경계를 선택하거나, 잘못된 가격을 고치지 않습니다. 요약은 금액이 0이어도 실제 티켓 ID를 셉니다. 가을, 봄, 판매가 없는 날짜를 요청하세요:
./desk close 2024-11-03
./desk close 2024-03-10
./desk close 2024-07-01
11월 3일에는 Harbor에 총 4750센트인 티켓 여섯 장이 있고, 베를린에는 총 3750센트인 네 장이 있습니다. 3월 10일에는 Harbor에 총 3750센트인 네 장이 있고, 베를린도 티켓 0장, 금액 0센트로 계속 표시됩니다. 7월 1일에는 장소마다 판매 없는 장소의 행이 하나씩 있으며 두 요약 모두 0입니다. SQL 오류는 이러한 유효한 판매 없음 행과 다릅니다.
같은 시계 값, 같은 가격, 중복된 장소 이름은 식별 규칙이 아닙니다. 원래 티켓 및 장소 ID를 유지하세요. DISTINCT로 서로 다른 영수증을 합치거나 원장을 총합만으로 대체하지 마세요. 합계뿐 아니라 행도 확인한 뒤 단계 검사를 실행하세요.
의미가 유지되는 보고서 인계하기
이 단계에서는 실제 마감 쿼리 행을 실제 CSV 파일 두 개로 내보내고, 연결의 표시 시간대 때문에 판매가 다른 장소 날짜로 이동하지 않음을 확인합니다.
계속 Terminal과 day-close 디렉터리를 사용하세요. SQL 파일 두 개를 유지하세요. --zone은 이번 데스크 호출에 한해 PostgreSQL 연결의 표시 TimeZone을 선택합니다. 장소에 저장된 시간대, 영업 날짜, 원본 티켓을 변경하지 않습니다. 다른 연결 시간대 두 개에서 같은 마감일을 비교하세요:
./desk close 2024-11-03 --zone Asia/Tokyo
./desk close 2024-11-03 --zone America/Los_Angeles
두 출력에는 여전히 같은 원래 티켓, 장소 날짜, 명시적인 UTC 구간, 합계가 들어 있습니다. 쿼리가 sold_at을 별도 시간대 처리 없이 date로 형 변환하거나, 현지 시계 표시를 만든 뒤 Z라고 표시하면 연결 시간대 변경으로 실수가 드러날 수 있습니다. 전역 데이터베이스 설정을 바꾸거나 원본 시간을 다시 쓰지 말고 쿼리를 수정하세요.
CSV는 머리글과 구분자로 나뉜 행을 저장하며, 따옴표 처리는 쉼표가 있는 값을 보호합니다. 데스크의 export 동작은 이 작업 공간의 close-YYYY-MM-DD.csv에 PostgreSQL 자체 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
두 파일은 명시된 아홉 열 원장을 유지합니다. 개수와 합계는 확인했던 실제 쿼리 결과와 일치합니다. Harbor/Berlin 순서로 가을에는 6/4750과 4/3750, 봄에는 4/3750과 0/0입니다. 반복된 01:30의 두 ID가 모두 가을 파일에 남고 무료 티켓도 실제 행으로 유지됩니다. 스크린샷이나 직접 입력한 총합은 이 사실들을 보존하지 못합니다.
windows.sql, daily-close.sql, 요청된 보고서 두 개와 원래 데이터베이스/도구를 보관하세요. 쿼리를 수정해도 이전 파일이 자동으로 갱신되지 않습니다. 해당 날짜를 명시적으로 다시 내보낸 다음 저장된 파일을 읽어 확인하세요. 보고서 파일 이름을 잘못 입력했다면 데이터베이스를 변경하지 말고 ls를 사용해 실제 이름으로 다시 시도하세요. 저장된 행을 확인한 뒤 최종 검사를 실행하세요.
요약
달력 날짜, 시계 표시, 실제 시점, IANA 시간대 규칙을 구분했습니다. 두 현지 자정을 독립적으로 변환하고, 반개구간으로 원래 티켓을 각각 한 번씩 보존하며, 판매가 없는 장소도 유지하고, UTC 시점을 명시한 실제 보고서를 내보냈습니다. 저장한 쿼리는 세션 TimeZone 변경에도 동작하고 파일 읽기 도구는 실제 저장된 행만 표시합니다. 이는 단순화된 과거 티켓 마감이며 결제 정산이나 임의의 모호한 시각 해석은 아닙니다.
