[PostgreSQL] interval 산술로 날짜 계산을 DB에서 끝낸다

설치·접속: PostgreSQL 설치와 접속

부제: "가입 후 30일 지난 사용자", 월 코호트, 이번 달 말일 같은 날짜 계산을 앱에서 for 루프로 돌리고 있을 때

-- 가입일이 흩어진 회원 테이블 (검증용 scratch)
CREATE TABLE member AS
SELECT g AS id, 'user'||g AS name,
       (now() - (g*2 || ' days')::interval - (g*7 || ' hours')::interval) AS signed_up_at
FROM generate_series(1,50) AS g;

-- age(): 가입 후 경과 기간을 년/월/일로
SELECT id, signed_up_at::date,
       age(now(), signed_up_at) AS since_signup
FROM member ORDER BY id LIMIT 5;

-- 가입 후 30일이 지난 사용자 (interval 비교)
SELECT count(*) AS over_30d
FROM member
WHERE signed_up_at < now() - interval '30 days';

 id | signed_up_at |      since_signup       
----+--------------+-------------------------
  1 | 2026-07-15   | 2 days 07:00:00.012376
  2 | 2026-07-13   | 4 days 14:00:00.012376
  3 | 2026-07-11   | 6 days 21:00:00.012376
  4 | 2026-07-08   | 9 days 04:00:00.012376
  5 | 2026-07-06   | 11 days 11:00:00.012376
(5 rows)

 over_30d 
----------
       37
(1 row)

age(now(), signed_up_at)는 두 시각의 차이를 "2 days", "6 days 21:00:00"처럼 사람이 읽는 년/월/일 interval로 준다(단순 뺄셈이 총 시간 간격을 주는 것과 대비). "가입 후 30일 지난 사용자"는 조건을 값이 아니라 now() - interval '30 days'라는 기준 시각으로 뒤집어 signed_up_at < 기준 으로 쓴다 — 이러면 signed_up_at에 함수를 안 씌우니 인덱스가 그대로 먹는다. 50명 중 37명이 걸렸다.

월 경계 계산은 date_trunc가 핵심이다. date_trunc('month', now())는 이번 달 1일 0시로 내림하고, 거기에 + interval '1 month'로 다음 달 1일, - interval '1 day'로 이번 달 말일을 얻는다. 말일을 "28/30/31" 분기로 세지 않는 게 요령이다.

-- 월 코호트 집계 + 월 경계 3종
SELECT date_trunc('month', signed_up_at)::date AS cohort_month, count(*)
FROM member GROUP BY 1 ORDER BY 1;

SELECT date_trunc('month', now())::date AS month_start,
       (date_trunc('month', now()) + interval '1 month')::date AS next_month_start,
       (date_trunc('month', now()) + interval '1 month' - interval '1 day')::date AS month_end;

 cohort_month | count 
--------------+-------
 2026-03-01   |     3
 2026-05-01   |    14
 2026-07-01   |     7
...

 month_start | next_month_start | month_end  
-------------+------------------+------------
 2026-07-01  | 2026-08-01       | 2026-07-31

이렇게도 쓴다

justify_interval로 '400 days' 같은 큰 interval을 월/일로 정규화한다.

SELECT justify_interval(interval '400 days 50 hours'), -- 1 year 1 mon 12 days 02:00:00
       justify_days(interval '95 days'),               -- 3 mons 5 days
       justify_hours(interval '50 hours');              -- 2 days 02:00:00

 

generate_series로 향후 3개월 정산일(매월 1일) 축을 만든다. (조합: generate_series 날짜 축)

SELECT g::date AS billing_date
FROM generate_series(date_trunc('month', now()),
                     date_trunc('month', now()) + interval '3 months',
                     interval '1 month') g;
-- 2026-07-01 / 2026-08-01 / 2026-09-01 / 2026-10-01

 

경과 '일수'를 정수로 뽑아야 하면 EXTRACT(epoch)를 86400으로 나눈다. (조합: extract)

SELECT id, (extract(epoch FROM now()-signed_up_at)/86400)::int AS days_since
FROM member ORDER BY id LIMIT 3;   -- 2 / 5 / 7

 

이번 주/오늘 경계도 date_trunc로. 리포트 "오늘 자정 이후" 필터에 쓴다.

SELECT date_trunc('week', now())::date AS week_start,
       date_trunc('day', now())        AS today_midnight;

 

읽기 가능한 실제 shop.users에도 그대로 적용된다(가입일 필터).

SELECT count(*) FROM users WHERE created_at < now() - interval '30 days';