Quick Reference

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS count
FROM orders
GROUP BY 1
ORDER BY 1;

시간별 집계는 date_trunc로 버킷을 만들고, 빈 구간까지 보여 줄 때만 generate_series를 날짜 축으로 만듭니다. TIMESTAMPTZ의 일자 경계는 세션 시간대에 따라 달라지므로, 조회는 보통 원본 열의 반열린 범위(>= start AND < end)로 제한합니다. 버킷 expression index가 필요하면 시간대를 고정한 immutable 표현식과 같은 query expression을 EXPLAIN으로 함께 검증합니다.

문법

date_trunc는 시간을 버킷으로 묶어 GROUP BY를 단순하게 만든다

date_trunc('day', ts)는 timestamp를 일(day) 단위 버킷으로 정규화합니다. 2026-04-02 15:23:01은 2026-04-02 00:00:00으로 잘립니다. 이렇게 하면 같은 날 모든 행이 같은 키를 갖게 되어 GROUP BY로 날짜별 집계가 가능해집니다. 'week', 'month', 'hour' 등 단위를 바꾸면 주별·월별·시간대별 리포트로 전환됩니다. 다만 TIMESTAMPTZ를 자를 때는 세션 TimeZone이 결과에 영향을 주므로, raw column index를 활용하는 필터와 버킷 집계를 같은 문제로 보지 않습니다.

-- 월별 매출 집계
SELECT
  date_trunc('month', created_at) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

date_trunc와 generate_series는 역할이 다르다

date_trunc는 기존 데이터의 시간을 일정 버킷으로 잘라 묶는 함수이고, generate_series는 없는 날짜까지 포함한 기준 축을 만드는 함수입니다. 실제 데이터만 요약하면 date_trunc만으로 충분하고, 빈 날짜도 보여야 하면 generate_series를 함께 써야 합니다.

-- 실제 데이터만 버킷화
SELECT date_trunc('day', created_at), COUNT(*)
FROM orders
GROUP BY 1;

-- 빈 날짜까지 포함한 축 생성
SELECT generate_series(CURRENT_DATE - 6, CURRENT_DATE, INTERVAL '1 day')::date;

generate_series는 연속 구간을 가상 테이블로 만든다

generate_series(start, stop, step)은 지정한 구간과 간격으로 값의 시퀀스를 반환하는 집합 반환 함수(SRF)입니다. 날짜 구간에 사용하면 하루 단위로 연속된 날짜 목록을 만들 수 있습니다. 이 가상 테이블을 실제 데이터와 LEFT JOIN하면 "기록이 없는 날도 0으로 채워진 시계열"을 만들 수 있습니다.

-- 지난 30일 날짜 시퀀스 생성
SELECT generate_series(
  CURRENT_DATE - 30,
  CURRENT_DATE,
  INTERVAL '1 day'
)::date AS day;

빠진 날짜를 채우지 않으면 시계열 리포트가 왜곡된다

실제 주문이 없는 날은 GROUP BY에서 행 자체가 사라집니다. 이를 그냥 두면 그래프에서 해당 날짜가 빠져 "매출이 갑자기 없다"처럼 보이거나, 정산 로직에서 누락이 발생합니다. generate_series로 날짜 뼈대를 먼저 만들고 LEFT JOIN으로 실제 데이터를 붙이면, 데이터가 없는 날도 0 또는 NULL로 명시적으로 표현됩니다. COALESCE로 NULL을 0으로 변환하면 최종 리포트가 완성됩니다.

WITH days AS (
  SELECT generate_series(
    CURRENT_DATE - 29,
    CURRENT_DATE,
    INTERVAL '1 day'
  )::date AS day
),
daily AS (
  SELECT
    date_trunc('day', created_at)::date AS day,
    COUNT(*) AS cnt
  FROM orders
  GROUP BY 1
)
SELECT
  d.day,
  COALESCE(o.cnt, 0) AS order_count
FROM days d
LEFT JOIN daily o ON d.day = o.day
ORDER BY d.day;

date_trunc와 generate_series는 집계와 축 생성이라는 역할이 다르다

date_trunc만 있으면 "있는 데이터"를 버킷별로 묶을 수 있고, generate_series가 있어야 "없던 날짜"까지 포함한 연속 축을 만들 수 있습니다. 리포트가 실제 집계만 필요한지, 빠진 날짜까지 채운 완전한 시계열이 필요한지 먼저 구분하면 설계가 단순해집니다.

-- 있는 주문만 날짜별 집계
SELECT date_trunc('day', created_at) AS day, COUNT(*)
FROM orders
GROUP BY 1;

-- 빈 날짜까지 채우는 뼈대 생성
SELECT generate_series(
  CURRENT_DATE - 6,
  CURRENT_DATE,
  INTERVAL '1 day'
)::date AS day;

TIMESTAMPTZ 집계는 날짜 기준 시간대를 먼저 정한다

TIMESTAMPTZ의 date_trunc와 날짜 cast는 세션 시간대에 따라 날짜 경계가 달라질 수 있습니다. 서버가 UTC인데 리포트 기준이 KST(UTC+9)라면, 집계용 local timestamp를 명시적으로 만들고 같은 기준으로 날짜 축도 만든다. 특정 로컬 날짜의 행을 빠르게 찾을 때는 변환 함수로 열을 감싸기보다 원본 TIMESTAMPTZ에 대한 시작·끝 instant 범위를 계산해 비교하는 편이 기본값이다.

-- KST 기준 날짜별 버킷: 결과는 timestamp without time zone
SELECT date_trunc('day', created_at AT TIME ZONE 'Asia/Seoul') AS day_kst
FROM orders;

-- UTC instant 범위로 필터: raw created_at index와 맞추기 쉽다
SELECT *
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-04-01 00:00:00+09'
  AND created_at <  TIMESTAMPTZ '2026-04-02 00:00:00+09';

체크포인트

주의할 점

SELECT date_trunc('day', created_at) AS day, COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 1;

이 결과에는 주문이 없던 날짜가 아예 나타나지 않습니다. "0건"이 아니라 "행 자체가 없음"이라서 리포트 해석이 달라질 수 있습니다.

SELECT generate_series(
  '2026-04-01'::date,
  '2026-04-30'::date,
  '1 month'
);

구간 단위와 step이 맞지 않으면 기대와 다른 축이 만들어집니다. 하루 단위 리포트인데 month step을 쓰거나, 주 단위 집계인데 day 버킷과 섞어 쓰기 시작하면 결과가 맞아 보여도 축 자체가 틀릴 수 있습니다.