🚨INCIDENT ALERT

HIGH

검색, 리포트, 배치 작업에서는 문자열과 날짜를 가공하는 쿼리가 자주 등장합니다. 함수 사용 위치를 잘못 잡으면 인덱스를 못 타거나 타임존 버그가 생깁니다. 문자열·날짜 함수를 정확히 쓰면 애플리케이션 코드를 줄이면서도 결과를 안정적으로 만들 수 있습니다.

이번 챕터에서 배울 것

문자열과 날짜 함수는 데이터 정제, 리포트 생성, 검색 기능 구현의 핵심 도구입니다. PostgreSQL 기준으로 설명하며 MySQL 차이점도 함께 다룹니다.

1문자열 함수 — TRIM, UPPER/LOWER, SUBSTRING, REPLACE, CONCAT
2패턴 추출 — SPLIT_PART, POSITION, LEFT/RIGHT
3정규식 함수 — REGEXP_REPLACE, REGEXP_MATCH
4날짜/시간 — NOW, CURRENT_DATE, DATE_TRUNC, EXTRACT
5기간 계산 — AGE, INTERVAL 덧셈, DATEDIFF
6날짜 포맷 — TO_CHAR, TO_DATE
7타임존 처리 — AT TIME ZONE
8PostgreSQL vs MySQL 함수 차이

문자열/날짜 함수 — 실무에서 가장 자주 쓰는 것들

데이터베이스에는 애플리케이션 코드 없이 SQL만으로 처리할 수 있는 강력한 내장 함수들이 있습니다. 문자열 정제, 날짜 계산, 기간 집계 등을 DB 레벨에서 처리하면 네트워크 전송과 애플리케이션 처리 비용을 줄일 수 있습니다.

💡개념

문자열 함수 실무 패턴 — 데이터 정제와 변환

외부 시스템에서 가져온 데이터에 공백이 섞여 있거나, 전화번호가 010-1234-5678과 01012345678 두 형식으로 혼재합니다. 이걸 애플리케이션에서 정제하면 모든 곳에서 처리 로직이 중복됩니다. SQL 문자열 함수로 DB 쿼리 단계에서 정제하면 한 곳에서 관리할 수 있습니다.

기본 문자열 함수

TRIM은 문자열의 앞뒤 공백을 제거합니다. LTRIM은 왼쪽만, RTRIM은 오른쪽만 제거하며, TRIM(BOTH '0' FROM ...) 형식으로 특정 문자를 제거할 수도 있습니다. INITCAP은 PostgreSQL 전용으로 각 단어 첫 글자를 대문자로 변환합니다.

SQL

SELECT
    TRIM('  안녕하세요  ')           AS trim_both,
    LTRIM('  안녕하세요  ')          AS trim_left,
    RTRIM('  안녕하세요  ')          AS trim_right,
    TRIM(BOTH '0' FROM '00042000')   AS trim_zeros;

SELECT
    UPPER('hello World')   AS upper_case,
    LOWER('Hello WORLD')   AS lower_case,
    INITCAP('hello world') AS title_case;

SELECT
    LENGTH('안녕하세요')        AS byte_length,
    CHAR_LENGTH('안녕하세요')   AS char_length,
    LENGTH('hello')             AS ascii_length;

OUTPUT

실행 완료 또는 조회 결과가 표시됩니다.

🔍실행 후 확인할 것

함수 적용 위치—WHERE 컬럼에 함수를 씌워 인덱스를 못 타지 않는지 확인합니다.
타임존 기준—저장·조회·표시 시간이 같은 기준인지 봅니다.
문자 결과—대소문자, 공백, 부분 문자열 처리가 기대와 맞는지 점검합니다.

문자열 자르기와 치환

SUBSTRING은 시작 위치와 길이로 부분 문자열을 추출합니다. SPLIT_PART는 PostgreSQL 전용으로 구분자 기준 N번째 토큰을 반환하며, 이메일 도메인 추출이나 날짜 파싱에 유용합니다.

SQL

SELECT
    SUBSTRING('Hello, World!' FROM 1 FOR 5)    AS first_5,
    SUBSTRING('Hello, World!' FROM 8)          AS from_8,
    SUBSTR('Hello, World!', 8, 5)              AS world;

SELECT
    REPLACE('010-1234-5678', '-', '')          AS no_dash,
    REPLACE('홍길동 선생님', ' 선생님', '')     AS name_only;

SELECT
    SPLIT_PART('hong@gmail.com', '@', 1)  AS username,
    SPLIT_PART('hong@gmail.com', '@', 2)  AS domain,
    SPLIT_PART('2024-03-15', '-', 1)      AS year,
    SPLIT_PART('2024-03-15', '-', 2)      AS month;

SELECT
    LEFT('010-1234-5678', 3)    AS area_code,
    RIGHT('010-1234-5678', 4)   AS last_4;

SELECT
    POSITION('@' IN 'user@example.com')     AS at_position,
    STRPOS('user@example.com', '@')         AS at_strpos;

문자열 연결과 포맷팅

CONCAT은 NULL을 빈 문자열로 처리하지만, || 연산자는 NULL이 하나라도 있으면 전체 결과가 NULL이 됩니다. CONCAT_WS는 NULL 항목을 건너뛰면서 구분자로 이어 붙일 때 유용합니다.

SQL

SELECT
    '성: ' || '홍' || ' 이름: ' || '길동'          AS 연결1,
    CONCAT('홍', '길동')                            AS 연결2,
    CONCAT_WS(', ', '서울', '강남구', NULL, '역삼동') AS 연결3;

SELECT
    LPAD('42', 8, '0')        AS zero_padded,
    RPAD('hello', 10, '.')    AS right_padded;

실무 데이터 정제

이메일에서 도메인을 추출하거나, 전화번호의 하이픈과 공백을 제거하고, 정규식으로 유효하지 않은 이메일을 찾는 쿼리는 데이터 정제 작업에서 반복적으로 사용됩니다. REGEXP_REPLACE의 네 번째 인자 'g'는 전체 치환(global)을 의미합니다.

SQL

SELECT
    email,
    LOWER(SPLIT_PART(email, '@', 2)) AS domain
FROM users
WHERE email IS NOT NULL;

SQL

UPDATE users
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g')
WHERE phone IS NOT NULL;

SQL

SELECT
    full_name,
    SPLIT_PART(full_name, ' ', 1) AS first_name,
    SPLIT_PART(full_name, ' ', 2) AS last_name
FROM users_import;

SQL

SELECT *
FROM users
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

WHERE email LIKE '%@gmail.com'처럼 앞에 % 와일드카드를 붙이면 PostgreSQL은 인덱스를 사용하지 못하고 전체 테이블 스캔(Seq Scan)을 수행합니다. 수백만 건 테이블에서 쿼리가 수초 이상 걸리는 원인이 됩니다.

해결: 도메인 기준 검색이 자주 필요하다면 SPLIT_PART(email, '@', 2)로 도메인을 별도 컬럼에 저장하고 인덱스를 겁니다. 또는 REVERSE(email) LIKE REVERSE('%@gmail.com')처럼 뒤집어서 앞 와일드카드를 제거하는 트릭을 쓸 수 있습니다. 전문 검색이 필요하다면 pg_trgm 확장의 GIN 인덱스를 활용합니다.

💡개념

날짜/시간 함수 — 기간 계산과 날짜 집계

가입일 기준으로 30일이 지난 사용자를 조회해야 합니다. 월별 신규 가입자를 집계해야 합니다. 두 날짜 사이의 일수를 계산해야 합니다. 날짜 함수가 PostgreSQL과 MySQL에서 다르게 생겼는데 어느 걸 써야 할지 모릅니다. 날짜 함수의 기본 패턴을 익혀두면 이런 요구사항을 쿼리 한 줄로 해결할 수 있습니다.

PostgreSQL vs MySQL 날짜 함수 차이

PostgreSQL과 MySQL은 날짜 함수 문법이 다릅니다. 팀이 두 DB를 혼용하거나 마이그레이션할 때 이 차이를 반드시 확인해야 합니다.

기능	PostgreSQL	MySQL
현재 날짜/시간	`NOW()`, `CURRENT_TIMESTAMP`	`NOW()`, `SYSDATE()`
날짜 자르기	`DATE_TRUNC('month', ts)`	`DATE_FORMAT(ts, '%Y-%m-01')`
부분 추출	`EXTRACT(YEAR FROM ts)`	`YEAR(ts)`, `MONTH(ts)`
날짜 차이(일)	`ts1 - ts2` (정수 반환)	`DATEDIFF(ts1, ts2)`
날짜 덧셈	`ts + INTERVAL '7 days'`	`ts + INTERVAL 7 DAY`
포맷 변환	`TO_CHAR(ts, 'YYYY-MM-DD')`	`DATE_FORMAT(ts, '%Y-%m-%d')`
경과 기간	`AGE(ts1, ts2)`	`TIMESTAMPDIFF(MONTH, ts2, ts1)`
타임존 변환	`ts AT TIME ZONE 'Asia/Seoul'`	`CONVERT_TZ(ts, '+00:00', '+09:00')`

현재 날짜/시간 함수

NOW()는 트랜잭션이 시작된 시각을 반환합니다. 트랜잭션 내에서 여러 번 호출해도 동일한 값을 반환합니다. 트랜잭션 내 실제 호출 시각이 필요하다면 CLOCK_TIMESTAMP()를 사용합니다.

SQL

SELECT
    NOW()             AS now_with_tz,
    CURRENT_TIMESTAMP AS current_ts,
    CURRENT_DATE      AS today,
    CURRENT_TIME      AS now_time,
    CLOCK_TIMESTAMP() AS real_now;

DATE_TRUNC — 시간 단위로 자르기

DATE_TRUNC는 타임스탬프를 지정한 단위의 시작점으로 내림합니다. 월별, 주별, 일별 집계 쿼리에서 GROUP BY와 함께 가장 많이 사용됩니다.

SQL

SELECT
    DATE_TRUNC('year',   NOW()) AS this_year_start,
    DATE_TRUNC('month',  NOW()) AS this_month_start,
    DATE_TRUNC('week',   NOW()) AS this_week_start,
    DATE_TRUNC('day',    NOW()) AS today_start,
    DATE_TRUNC('hour',   NOW()) AS this_hour_start,
    DATE_TRUNC('minute', NOW()) AS this_minute_start;

SQL

SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY 1;

EXTRACT — 날짜/시간 부분 추출

EXTRACT는 타임스탬프에서 특정 필드(연, 월, 일, 시, 요일 등)를 숫자로 추출합니다. DOW는 요일(0=일요일, 6=토요일), EPOCH는 Unix timestamp(초)를 반환합니다.

SQL

SELECT
    EXTRACT(YEAR  FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY   FROM NOW()) AS day,
    EXTRACT(HOUR  FROM NOW()) AS hour,
    EXTRACT(DOW   FROM NOW()) AS day_of_week,
    EXTRACT(DOY   FROM NOW()) AS day_of_year,
    EXTRACT(EPOCH FROM NOW()) AS unix_timestamp;

SQL

SELECT
    EXTRACT(DOW FROM created_at) AS day_of_week,
    TO_CHAR(created_at, 'Day')   AS day_name,
    COUNT(*)                     AS order_count
FROM orders
GROUP BY 1, 2
ORDER BY 1;

날짜 계산과 INTERVAL

PostgreSQL에서 날짜 연산은 INTERVAL 타입을 사용합니다. AGE(ts1, ts2)는 두 타임스탬프 사이의 경과 기간을 interval 형식으로 반환하며, AGE(NOW())처럼 인자 하나만 전달하면 현재 시각 기준으로 계산합니다.

SQL

SELECT
    NOW() + INTERVAL '7 days'          AS next_week,
    NOW() - INTERVAL '30 days'         AS last_month,
    NOW() + INTERVAL '1 year 3 months' AS next_period,
    CURRENT_DATE + 7                   AS also_next_week;

SQL

SELECT
    AGE('2024-03-15', '2000-06-15')  AS age_interval,
    AGE(NOW(), created_at)           AS account_age
FROM users WHERE id = 1;

SQL

SELECT
    EXTRACT(DAY FROM AGE('2024-03-15', '2024-01-01')) AS days_diff,
    ('2024-03-15'::DATE - '2024-01-01'::DATE)         AS date_subtraction;

SQL

SELECT id, name, created_at
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC;

SQL

SELECT id, name, last_login
FROM users
WHERE last_login < NOW() - INTERVAL '90 days'
   OR last_login IS NULL;

TO_CHAR — 날짜 포맷 변환

TO_CHAR는 숫자와 타임스탬프를 사람이 읽기 좋은 형식의 문자열로 변환합니다. 리포트 출력이나 레이블 생성에 사용합니다.

SQL

SELECT
    TO_CHAR(NOW(), 'YYYY-MM-DD')             AS iso_date,
    TO_CHAR(NOW(), 'YYYY년 MM월 DD일')       AS korean_date,
    TO_CHAR(NOW(), 'HH24:MI:SS')             AS time_24h,
    TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')  AS full_datetime,
    TO_CHAR(1234567.89, '999,999,999.99')    AS number_format;

SQL

SELECT
    TO_CHAR(created_at, 'YYYY-MM')                  AS month_label,
    COUNT(*)                                         AS order_count,
    TO_CHAR(SUM(amount), 'FM999,999,999')            AS revenue_formatted
FROM orders
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
ORDER BY 1;

타임존 처리 — TIMESTAMPTZ 사용 권장

TIMESTAMP 타입은 타임존 정보 없이 입력값을 그대로 저장합니다. TIMESTAMPTZ는 UTC로 저장하고 조회 시 세션 타임존으로 변환합니다. 운영 서비스에서는 항상 TIMESTAMPTZ를 사용해야 타임존 문제를 피할 수 있습니다.

SQL

SHOW timezone;
SELECT current_setting('TimeZone');

SQL

SELECT
    NOW()                                  AS utc_now,
    NOW() AT TIME ZONE 'Asia/Seoul'        AS seoul_time,
    NOW() AT TIME ZONE 'America/New_York'  AS ny_time;

SQL

CREATE TABLE events (
    id       BIGSERIAL PRIMARY KEY,
    title    VARCHAR(200) NOT NULL,
    event_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

SQL

INSERT INTO events (title, event_at)
VALUES ('서울 컨퍼런스', '2024-06-15 09:00:00 Asia/Seoul');

SQL

SELECT event_at FROM events;

SELECT event_at AT TIME ZONE 'Asia/Seoul' FROM events;

서버가 UTC로 설정된 환경에서 WHERE created_at::date = '2024-03-15'로 한국 시간 기준 3월 15일 데이터를 조회했는데, 한국 시간 자정(00:00~08:59 KST)에 생성된 데이터가 누락되었습니다. UTC 기준으로 이 시간대는 3월 14일이기 때문입니다.

해결: 날짜 비교 시 항상 타임존을 명시합니다. WHERE created_at AT TIME ZONE 'Asia/Seoul' >= '2024-03-15 00:00:00' AND created_at AT TIME ZONE 'Asia/Seoul' < '2024-03-16 00:00:00'처럼 작성하거나, DATE_TRUNC와 타임존 변환을 함께 사용합니다. 컬럼 타입은 항상 TIMESTAMPTZ를 사용하고 애플리케이션 서버의 타임존도 UTC로 통일하는 것이 장기적으로 가장 안전합니다.

💼

실무 맥락날짜 함수로 월별 리포트와 사용자 가입 코호트 분석 쿼리 작성

현업 패턴

월별 매출 리포트는 DATE_TRUNC('month', created_at)으로 월 단위로 집계하고, 신규 가입자 코호트 분석은 가입월과 활동월을 각각 DATE_TRUNC로 잘라서 조인합니다. 예를 들어 "2024년 1월에 가입한 사용자가 각 월에 얼마나 재방문했는가"를 분석하는 코호트 리텐션 쿼리는 DATE_TRUNC('month', signup_at) AS cohort_month와 DATE_TRUNC('month', last_active_at) AS activity_month를 GROUP BY로 집계하는 구조입니다. EXTRACT(EPOCH FROM AGE(activity_month, cohort_month)) / 2592000으로 코호트 이후 몇 달이 지났는지 계산해 피벗 테이블을 만들 수 있습니다.

다음 모듈에서는 뷰(View)와 Stored Procedure의 운영 관점 장단점과 언제 사용할지 판단 기준을 다룹니다.

[Database] 실무에서 가장 많이 쓰는 날짜 및 문자열 함수 활용법