숏컷 코드

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

문법

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' 등 단위를 바꾸면 주별·월별·시간대별 리포트로 그대로 전환됩니다. 실행 계획에서 date_trunc는 함수 호출이므로, 해당 컬럼 인덱스를 그대로 쓰지 못하고 표현식 인덱스가 필요합니다.

-- 월별 매출 집계
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');

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;

generate_series의 타임존 처리에 주의해야 한다

generate_series에 timestamptz를 넘기면 타임존을 고려해 구간을 생성합니다. 서버 타임존이 UTC이고 데이터는 KST(UTC+9) 기준으로 기록된 경우, 날짜 경계가 어긋날 수 있습니다. AT TIME ZONE 변환을 일관되게 적용하거나, 세션 타임존을 설정한 뒤 쿼리를 실행하는 방식으로 날짜 경계를 맞춰야 합니다.

-- KST 기준으로 날짜 경계를 맞춘 시계열 생성
SELECT generate_series(
  (CURRENT_DATE - 6) AT TIME ZONE 'Asia/Seoul',
  CURRENT_DATE AT TIME ZONE 'Asia/Seoul',
  INTERVAL '1 day'
) AS day_kst;

체크포인트

주의할 점

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 버킷과 섞어 쓰기 시작하면 결과가 맞아 보여도 축 자체가 틀릴 수 있습니다.