본문 바로가기
빌드노트 검색

빌드노트

PostgreSQL 일별 주문 집계가 연결 시간대에 따라 달라지는 이유

timestamptz를 바로 date로 바꾸면 연결의 TimeZone에 따라 날짜별 주문 수가 달라진다. 임시 PostgreSQL 18.6에서 자정 경계의 네 주문을 비교하고, UTC 통계와 서울 사용자 달력 중 기준을 정해 날짜 추출 전에 AT TIME ZONE을 적용하는 방법을 설명한다.

PostgreSQL에 주문 시각을 UTC로 넣었는데, 같은 일별 집계가 연결에 따라 다른 날짜의 주문 수를 반환할 수 있다. 열이 timestamptz이고 집계식이 ordered_at::date라면 집계를 실행하는 연결에서 SHOW TimeZone;을 확인한다. UTC로 시각을 저장하는 것과 UTC 기준으로 날짜를 뽑는 것은 별개다.

먼저 보고서의 하루를 정해야 한다. 서울 사용자에게 보여 줄 주문 달력이라면 날짜를 뽑기 전에 서울 시간으로 변환한다. 다음은 주문 시각 열이 timestamptz인 테이블의 집계식이다.

SELECT (ordered_at AT TIME ZONE 'Asia/Seoul')::date AS order_day,
       count(*) AS orders
FROM orders
GROUP BY (ordered_at AT TIME ZONE 'Asia/Seoul')::date
ORDER BY order_day;

UTC 날짜별 통계가 목적이라면 두 곳의 'Asia/Seoul'을 모두 'UTC'로 바꾼다. 사용자마다 시간대가 다른 보고서에서는 해당 사용자의 시간대를 적용해야 한다. 저장된 시각만으로 어느 기준이 맞는지 결정할 수는 없다.

같은 네 주문에서 3건과 1건이 뒤바뀌었다

실제 주문 장애 기록이 아니라, 서울 자정과 UTC 자정의 앞뒤에 합성 주문 네 건을 놓은 실험이다. 새 Docker 컨테이너의 PostgreSQL 18.6에서 실행했다. 데이터를 고치지 않고 같은 연결의 시간대 설정만 바꿨다.

직접 비교하려면 기존 orders 테이블이 없는 전용 실습 DB에 연결한다. 아래 SQL을 한 연결에서 실행한다. 임시 테이블은 그 연결에서만 보이며, 마지막의 ROLLBACK이 실습 변경과 시간대 설정을 되돌린다. SET LOCAL은 현재 트랜잭션 동안만 적용된다. 모든 입력에 TIMESTAMPTZ와 +00을 명시해 입력 시각을 연결 시간대에 맡기지 않았다. SET LOCAL의 적용 범위 (새 탭에서 열림)

BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
CREATE TEMP TABLE orders (
    id integer PRIMARY KEY,
    ordered_at timestamptz NOT NULL
) ON COMMIT DROP;
INSERT INTO orders VALUES
    (1, TIMESTAMPTZ '2026-10-19 14:59:59+00'),
    (2, TIMESTAMPTZ '2026-10-19 15:00:00+00'),
    (3, TIMESTAMPTZ '2026-10-19 23:59:59+00'),
    (4, TIMESTAMPTZ '2026-10-20 00:00:00+00');

SET LOCAL TIME ZONE 'UTC';
SELECT ordered_at::date AS order_day, count(*) AS orders
FROM orders GROUP BY ordered_at::date ORDER BY order_day;

SET LOCAL TIME ZONE 'Asia/Seoul';
SELECT ordered_at::date AS order_day, count(*) AS orders
FROM orders GROUP BY ordered_at::date ORDER BY order_day;
ROLLBACK;

두 SELECT의 실제 출력이다. 먼저 UTC 연결에서는 19일에 3건, 20일에 1건이 묶였다.

order_day,orders
2026-10-19,3
2026-10-20,1

서울 연결에서는 같은 네 행이 19일 1건, 20일 3건으로 묶였다. 여기서는 비교하기 쉽게 psql의 CSV 출력을 사용했다. 다른 SQL 도구에서는 표시 형식이 달라도 날짜와 개수를 비교하면 된다. psql의 CSV 출력 옵션 (새 탭에서 열림)

order_day,orders
2026-10-19,1
2026-10-20,3

2번 주문이 경계를 보여 준다. 2026-10-19 15:00:00+00은 서울에서 2026-10-20 00:00:00+09다. 이번 실행에서 두 설정의 epoch 초는 모두 1792422000.000000이었다. 같은 순간인데 날짜가 달라진 것이다. 총 네 건이라는 사실만 검사하면 이 차이를 놓친다.

날짜를 자르기 전에 기준 시간대를 적용한다

PostgreSQL 18 문서에서 timestamptz는 timestamp with time zone의 별칭이다. 내부에는 UTC로 환산한 시각을 저장하며, 입력에 쓴 시간대 이름이나 오프셋을 따로 보존하지 않는다. 출력은 연결의 TimeZone을 따른다. 이번 실험의 직접 날짜 변환도 그 설정에 따라 달라졌다. 날짜·시간 자료형의 입력과 출력 규칙 (새 탭에서 열림)

앞의 수정식은 timestamptz에 AT TIME ZONE 'Asia/Seoul'을 적용해 서울의 날짜와 시계로 표현한 timestamp without time zone을 만든다. 이어서 ::date로 날짜만 남긴다. 실제 반환 자료형도 timestamp with time zone → timestamp without time zone → date 순서로 확인했다. 시간대 변환 → 날짜 추출 → 집계 순서로 처리하는 이유다. AT TIME ZONE의 입력형별 반환형 (새 탭에서 열림)

같은 네 행에 명시적 변환을 넣고 두 연결 설정에서 집계한 결과도 확인했다.

  • 'UTC'를 명시한 집계는 두 설정 모두 10월 19일 3건, 20일 1건이었다.
  • 'Asia/Seoul'을 명시한 집계는 두 설정 모두 10월 19일 1건, 20일 3건이었다.

직접 재현할 때는 위 실습 SQL의 두 SELECT를 첫머리의 서울 집계 쿼리로 각각 교체한다. UTC 집계도 같은 방법으로 비교할 수 있다. 선택한 기준이 같다면 연결 시간대를 바꿔도 날짜별 개수가 유지되는지 본다.

이미 UTC 날짜별로 합친 개수에 서울 시간대를 적용해서는 경계의 주문을 다시 나눌 수 없다. 원래 시각이 사라졌기 때문이다. 보고서 기준을 바꿀 때는 각 주문의 시각에서 날짜를 다시 만들어 집계한다.

내 쿼리의 자료형과 조회 조건도 맞아야 한다

실제 열이 timestamp without time zone이면 위 수정식을 그대로 적용하지 않는다. 이 입력형에서 AT TIME ZONE은 주어진 숫자를 지정 지역의 시각으로 해석해 timestamptz를 만드는 반대 방향의 연산이다. 열 이름에 utc가 들어 있거나 화면에 +00이 보였다는 기억 대신 실제 열 자료형과 저장 규칙을 확인한다. 입력형에 따른 차이는 앞의 연산자 문서에 명시돼 있다.

열이 timestamptz인데 명시적 변환 뒤에도 개수가 다르다면 GROUP BY만 보지 않는다. 예를 들어 WHERE ordered_at::date = DATE '2026-10-20'가 남아 있으면, 집계에 들어갈 행을 고르는 단계부터 연결 시간대에 의존한다. 날짜를 추출하는 필터도 보고서와 같은 시간대를 써야 한다. 이 예제에서 서울 기준 20일은 UTC의 19일 15시 이상, 20일 15시 미만에 해당한다.

이번에 확인한 범위는 PostgreSQL 18.6의 네 합성 행, UTC·서울 두 연결 설정, 날짜별 개수와 반환 자료형이다. 실제 애플리케이션의 연결 풀이나 드라이버가 어떤 시간대를 설정하는지는 확인하지 않았다. 설정을 확인할 때도 집계를 실행하는 바로 그 연결에서 확인해야 한다. 다른 지역의 일광 절약 시간 전환일과 대량 데이터의 실행 계획은 이번 실험 범위에 포함하지 않았다.

← 전체 글 보기