PostgreSQL date_trunc 함수 완벽 가이드

2025년 12월 11일

database

# PostgreSQL# Database# SQL# Date

date_trunc 함수란?

date_trunc는 PostgreSQL에서 제공하는 날짜/시간 함수로, 타임스탬프나 날짜를 지정된 단위로 절삭(truncate)하는 함수입니다. 예를 들어, 특정 날짜의 시간을 모두 0으로 만들거나, 월의 첫째 날로 만들거나, 연도의 시작일로 만드는 등의 작업을 쉽게 수행할 수 있습니다.

이 함수는 주로 날짜/시간 데이터를 특정 단위로 그룹화하거나, 시계열 데이터 분석, 리포트 생성 등에 매우 유용하게 사용됩니다.

기본 문법

date_trunc(precision, source [, timezone])

매개변수

precision: 절삭할 단위를 지정하는 문자열
- microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium
source: 절삭할 날짜/시간 값 (timestamp, timestamptz, interval 타입)
timezone (선택): 시간대 지정 (timestamptz를 사용할 때만)

반환 타입

source가 timestamp 타입이면 timestamp 반환
source가 timestamptz 타입이면 timestamptz 반환
source가 interval 타입이면 interval 반환

사용 예시

기본 사용법

-- 현재 시간을 일 단위로 절삭 (시간, 분, 초를 00:00:00으로)
SELECT date_trunc('day', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-12-15 00:00:00

-- 현재 시간을 월 단위로 절삭 (해당 월의 1일 00:00:00)
SELECT date_trunc('month', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-12-01 00:00:00

-- 현재 시간을 연 단위로 절삭 (해당 연도의 1월 1일 00:00:00)
SELECT date_trunc('year', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-01-01 00:00:00

시간 단위 절삭

-- 시간 단위로 절삭
SELECT date_trunc('hour', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-12-15 14:00:00

-- 분 단위로 절삭
SELECT date_trunc('minute', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-12-15 14:35:00

-- 초 단위로 절삭
SELECT date_trunc('second', TIMESTAMP '2023-12-15 14:35:20.123456');
-- 결과: 2023-12-15 14:35:20

주 단위 절삭

-- 주 단위로 절삭 (해당 주의 월요일 00:00:00)
SELECT date_trunc('week', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-12-11 00:00:00 (2023-12-15가 금요일이라면, 그 주의 월요일)

참고: PostgreSQL의 week 단위 절삭은 ISO 8601 표준을 따르며, 주의 시작은 월요일입니다.

분기 단위 절삭

-- 분기 단위로 절삭 (해당 분기의 첫 날 00:00:00)
SELECT date_trunc('quarter', TIMESTAMP '2023-12-15 14:35:20');
-- 결과: 2023-10-01 00:00:00 (4분기 시작일)

SELECT date_trunc('quarter', TIMESTAMP '2023-05-20 10:30:00');
-- 결과: 2023-04-01 00:00:00 (2분기 시작일)

시간대를 고려한 절삭

-- 시간대를 지정하여 절삭
SELECT date_trunc('day', TIMESTAMPTZ '2023-12-15 14:35:20+09', 'Asia/Seoul');
-- 결과: 2023-12-15 00:00:00+09

-- UTC 시간대로 절삭
SELECT date_trunc('day', TIMESTAMPTZ '2023-12-15 14:35:20+09', 'UTC');
-- 결과: 2023-12-15 00:00:00+00

실무 활용 예시

1. 일별 집계 쿼리

-- 일별 주문 건수와 총액 집계
SELECT
    date_trunc('day', order_date) AS order_day,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY date_trunc('day', order_date)
ORDER BY order_day;

2. 월별 리포트 생성

-- 월별 사용자 가입 수 집계
SELECT
    date_trunc('month', created_at) AS signup_month,
    COUNT(*) AS new_users
FROM users
WHERE created_at >= '2023-01-01'
GROUP BY date_trunc('month', created_at)
ORDER BY signup_month;

3. 시간대별 트래픽 분석

-- 시간대별 API 요청 수 분석
SELECT
    date_trunc('hour', request_time) AS request_hour,
    COUNT(*) AS request_count,
    AVG(response_time) AS avg_response_time
FROM api_logs
WHERE request_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY date_trunc('hour', request_time)
ORDER BY request_hour;

4. 주간 활성 사용자 분석

-- 주별 활성 사용자 수 (중복 제거)
SELECT
    date_trunc('week', login_time) AS week_start,
    COUNT(DISTINCT user_id) AS active_users
FROM user_logins
WHERE login_time >= '2023-01-01'
GROUP BY date_trunc('week', login_time)
ORDER BY week_start;

5. 분기별 매출 비교

-- 분기별 매출 집계 및 전년 동기 대비 비교
WITH quarterly_sales AS (
    SELECT
        date_trunc('quarter', order_date) AS quarter,
        SUM(total_amount) AS sales
    FROM orders
    GROUP BY date_trunc('quarter', order_date)
)
SELECT
    quarter,
    sales,
    LAG(sales, 4) OVER (ORDER BY quarter) AS prev_year_sales,
    ROUND(((sales - LAG(sales, 4) OVER (ORDER BY quarter)) /
           LAG(sales, 4) OVER (ORDER BY quarter) * 100), 2) AS growth_rate
FROM quarterly_sales
ORDER BY quarter;

6. 특정 시간 범위의 데이터 조회

-- 오늘의 시작 시간부터 현재까지의 데이터 조회
SELECT *
FROM events
WHERE event_time >= date_trunc('day', NOW())
  AND event_time < NOW();

-- 이번 달의 데이터만 조회
SELECT *
FROM transactions
WHERE transaction_date >= date_trunc('month', NOW())
  AND transaction_date < date_trunc('month', NOW() + INTERVAL '1 month');

다른 데이터베이스와의 비교

MySQL

MySQL에는 date_trunc 함수가 없지만, 유사한 기능을 다른 함수로 구현할 수 있습니다:

-- PostgreSQL
SELECT date_trunc('day', created_at) FROM orders;

-- MySQL 대안
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
SELECT DATE(created_at) FROM orders;

-- PostgreSQL (월 단위)
SELECT date_trunc('month', created_at) FROM orders;

-- MySQL 대안
SELECT DATE_FORMAT(created_at, '%Y-%m-01') FROM orders;

Oracle

Oracle에는 TRUNC 함수가 있습니다:

-- PostgreSQL
SELECT date_trunc('month', order_date) FROM orders;

-- Oracle
SELECT TRUNC(order_date, 'MONTH') FROM orders;

SQL Server

SQL Server에는 DATETRUNC 함수가 SQL Server 2022부터 추가되었습니다:

-- SQL Server 2022+
SELECT DATETRUNC(month, order_date) FROM orders;

-- SQL Server (이전 버전)
SELECT DATEADD(month, DATEDIFF(month, 0, order_date), 0) FROM orders;

성능 최적화 팁

1. 인덱스 활용

date_trunc를 WHERE 절에서 사용할 때는 함수 기반 인덱스(Function-based Index)를 생성하면 성능을 향상시킬 수 있습니다:

-- 함수 기반 인덱스 생성
CREATE INDEX idx_orders_day ON orders (date_trunc('day', order_date));

-- 쿼리에서 활용
SELECT *
FROM orders
WHERE date_trunc('day', order_date) = '2023-12-15';

2. 범위 검색으로 변환

가능하다면 date_trunc 대신 범위 검색을 사용하면 일반 인덱스를 활용할 수 있습니다:

-- 비효율적 (함수 기반 인덱스 필요)
SELECT *
FROM orders
WHERE date_trunc('day', order_date) = '2023-12-15';

-- 효율적 (일반 인덱스 활용 가능)
SELECT *
FROM orders
WHERE order_date >= '2023-12-15'
  AND order_date < '2023-12-16';

주의사항

1. 시간대 처리

timestamptz 타입을 사용할 때는 시간대 처리에 주의해야 합니다:

-- 세션 시간대에 따라 결과가 달라질 수 있음
SELECT date_trunc('day', NOW());

-- 명시적으로 시간대를 지정하는 것이 안전
SELECT date_trunc('day', NOW(), 'Asia/Seoul');

2. NULL 처리

date_trunc는 NULL 입력에 대해 NULL을 반환합니다:

SELECT date_trunc('day', NULL);
-- 결과: NULL

3. interval 타입 사용 시

interval 타입에 대해서도 date_trunc를 사용할 수 있지만, 동작이 약간 다릅니다:

SELECT date_trunc('hour', INTERVAL '2 days 3 hours 30 minutes');
-- 결과: 2 days 03:00:00

마치며

date_trunc 함수는 PostgreSQL에서 날짜/시간 데이터를 다룰 때 매우 유용한 함수입니다. 특히 시계열 데이터 분석, 리포트 생성, 데이터 집계 등의 작업에서 필수적으로 사용되는 함수이므로, 다양한 활용법을 숙지해두면 실무에서 큰 도움이 됩니다.

데이터베이스마다 날짜 절삭 함수의 이름과 사용법이 다르므로, 사용하는 DBMS의 문서를 참고하여 적절한 함수를 선택하는 것이 중요합니다.

Pak Min gi