SQL 날짜/시간 함수 완벽 가이드

SQL에서 날짜와 시간을 다루는 필수 함수들을 초급자도 쉽게 이해할 수 있도록 설명합니다. 현재 날짜 조회부터 날짜 계산, 포맷팅, 타임존 처리까지 실무에서 바로 활용할 수 있는 6가지 핵심 기능을 다룹니다.

---

목차

1. 현재_날짜_시간_조회
2. DATE_FORMAT으로_날짜_포맷팅
3. DATEDIFF로_날짜_차이_계산
4. DATE_ADD_DATE_SUB_날짜_연산
5. EXTRACT로_날짜_부분_추출
6. 타임존_처리하기

---

1. 현재_날짜_시간_조회

시작하며

여러분이 회원가입 시스템을 만들 때, 사용자가 가입한 정확한 시간을 기록해야 하는 상황을 겪어본 적 있나요? 또는 주문이 들어온 시간, 게시글이 작성된 시간을 데이터베이스에 저장해야 할 때 말이죠.

이런 문제는 실제 개발 현장에서 거의 매일 발생합니다. 날짜와 시간 정보는 모든 애플리케이션의 기본이 되는 데이터이기 때문이죠.

하지만 막상 코드를 작성하려고 하면 "현재 시간을 어떻게 가져오지?"라는 의문이 생깁니다. 바로 이럴 때 필요한 것이 NOW()와 CURRENT_DATE 함수입니다.

이 함수들을 사용하면 SQL 쿼리 실행 시점의 정확한 날짜와 시간을 자동으로 가져올 수 있어서, 수동으로 입력할 필요가 전혀 없습니다.

개요

간단히 말해서, NOW() 함수는 현재 날짜와 시간을 모두 포함한 값을 반환하고, CURRENT_DATE는 시간 없이 날짜만 반환하는 함수입니다. 왜 이 함수들이 필요할까요?

실무에서는 데이터가 생성된 시점을 정확히 기록하는 것이 매우 중요합니다. 예를 들어, 전자상거래 사이트에서 주문 데이터를 분석할 때 "오늘 들어온 주문", "이번 달 매출" 같은 정보를 계산하려면 정확한 시간 정보가 필수입니다.

기존에는 애플리케이션 코드에서 날짜를 생성해서 SQL에 전달했다면, 이제는 데이터베이스가 직접 현재 시간을 기록할 수 있습니다. 이렇게 하면 서버 시간과 데이터베이스 시간이 다른 문제도 방지할 수 있죠.

이 함수들의 핵심 특징은 첫째, 자동으로 현재 시간을 가져온다는 점, 둘째, 데이터베이스 서버의 시간을 기준으로 한다는 점, 셋째, INSERT나 UPDATE 시 기본값으로 설정할 수 있다는 점입니다. 이러한 특징들이 데이터의 정확성과 일관성을 보장하는 데 매우 중요합니다.

코드 예제

-- 회원 가입 시 현재 시간 자동 기록
INSERT INTO users (username, email, created_at, signup_date)
VALUES ('김철수', 'chulsoo@example.com', NOW(), CURRENT_DATE);

-- 현재 날짜/시간 조회하기
SELECT
    NOW() AS 현재날짜시간,           -- 2024-01-15 14:30:25
    CURRENT_DATE AS 오늘날짜,        -- 2024-01-15
    CURRENT_TIME AS 현재시간,        -- 14:30:25
    CURRENT_TIMESTAMP AS 타임스탬프;  -- 2024-01-15 14:30:25

-- 오늘 생성된 주문 조회
SELECT * FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

설명

이것이 하는 일: 이 코드는 데이터베이스에 새로운 사용자를 등록하면서 가입한 정확한 시간을 자동으로 기록합니다. 마치 여러분이 손목시계를 보면서 "지금이 몇 시지?"라고 확인하는 것처럼, SQL도 현재 시간을 확인해서 데이터에 저장하는 거죠.

첫 번째 단계에서 INSERT 문의 NOW()는 쿼리가 실행되는 그 순간의 정확한 날짜와 시간을 가져옵니다. 예를 들어 2024년 1월 15일 오후 2시 30분 25초에 실행되면 '2024-01-15 14:30:25' 형태로 저장됩니다.

왜 이렇게 하냐면, 나중에 "이 회원이 언제 가입했지?"를 정확히 알 수 있기 때문입니다. 두 번째로 SELECT 쿼리 부분에서는 여러 가지 날짜/시간 함수들을 비교해서 보여줍니다.

NOW()와 CURRENT_TIMESTAMP는 같은 결과를 주지만, CURRENT_DATE는 시간 부분을 제외하고 날짜만, CURRENT_TIME은 반대로 시간만 반환합니다. 이렇게 구분하는 이유는 용도에 따라 필요한 정보가 다르기 때문이죠.

세 번째 단계에서는 실무에서 가장 많이 사용하는 패턴인 "오늘 생성된 데이터 찾기"를 보여줍니다. WHERE 절에서 DATE(created_at) = CURRENT_DATE를 사용하면, created_at에 저장된 시간 부분은 무시하고 날짜만 비교해서 오늘 생성된 모든 주문을 찾을 수 있습니다.

여러분이 이 코드를 사용하면 수동으로 날짜를 입력하는 실수를 방지할 수 있고, 서버 시간과 데이터베이스 시간을 일치시킬 수 있으며, 자동화된 시간 기록으로 데이터 분석이 훨씬 정확해집니다. 특히 여러 서버가 다른 타임존에 있을 때도 일관된 시간 기준을 유지할 수 있다는 장점이 있습니다.

실전 팁

💡 테이블 생성 시 created_at 컬럼에 DEFAULT NOW()를 설정하면 INSERT 시 값을 명시하지 않아도 자동으로 현재 시간이 입력됩니다.

💡 NOW()는 쿼리가 시작된 시점의 시간을 반환하므로, 같은 쿼리 내에서 여러 번 호출해도 항상 같은 값을 반환합니다. 실시간으로 변하는 시간이 필요하면 SYSDATE()를 사용하세요.

💡 날짜만 비교할 때는 DATE(컬럼명) = CURRENT_DATE 형태로 사용하세요. 시간 부분까지 비교하면 정확히 일치하는 레코드만 찾게 되어 원하는 결과를 얻지 못할 수 있습니다.

💡 타임존 문제를 피하려면 UTC 시간으로 저장하고 애플리케이션에서 로컬 시간으로 변환하는 것이 좋습니다. MySQL에서는 UTC_TIMESTAMP() 함수를 사용하세요.

💡 성능을 위해 날짜 컬럼에는 인덱스를 생성하세요. 특히 WHERE 절에서 날짜 범위 검색을 자주 하는 경우 인덱스가 없으면 쿼리가 매우 느려집니다.

---

2. DATE_FORMAT으로_날짜_포맷팅

시작하며

여러분이 고객에게 보내는 이메일에 "주문일: 2024-01-15 14:30:25"라고 쓰면 어떨까요? 너무 기계적이고 읽기 불편하죠.

"주문일: 2024년 1월 15일 오후 2시 30분"이라고 쓰는 게 훨씬 자연스럽습니다. 이런 문제는 데이터베이스에서 날짜를 조회할 때 항상 발생합니다.

데이터베이스는 날짜를 표준 형식으로 저장하지만, 사용자에게 보여줄 때는 보기 좋게 변환해야 하기 때문이죠. 프로그래밍 언어에서 변환할 수도 있지만, SQL에서 바로 처리하면 훨씬 간단합니다.

바로 이럴 때 필요한 것이 DATE_FORMAT 함수입니다. 이 함수를 사용하면 데이터베이스에 저장된 날짜를 원하는 형태의 문자열로 자유롭게 변환할 수 있습니다.

개요

간단히 말해서, DATE_FORMAT은 날짜 데이터를 여러분이 원하는 형식의 문자열로 바꿔주는 함수입니다. 마치 같은 내용을 한글로 쓸지 영어로 쓸지 선택하는 것처럼 말이죠.

왜 이 함수가 필요할까요? 실무에서는 같은 날짜 데이터를 다양한 형식으로 보여줘야 합니다.

예를 들어, 관리자 페이지에서는 "2024-01-15"로 간단히 표시하고, 고객용 영수증에는 "2024년 1월 15일 월요일"로 자세히 표시하며, API 응답에는 "20240115" 형태로 전달해야 할 수 있습니다. 기존에는 데이터베이스에서 날짜를 가져온 후 애플리케이션 코드에서 일일이 변환했다면, 이제는 SQL 쿼리에서 바로 원하는 형식으로 변환할 수 있습니다.

이렇게 하면 코드가 훨씬 간결해지고, 데이터베이스에서 이미 가공된 데이터를 받으므로 효율적입니다. DATE_FORMAT의 핵심 특징은 첫째, 다양한 포맷 지정자(%Y, %m, %d 등)를 조합할 수 있다는 점, 둘째, 한 번의 쿼리로 여러 형식을 동시에 만들 수 있다는 점, 셋째, 언어 설정에 따라 월 이름이나 요일을 현지화할 수 있다는 점입니다.

이러한 특징들이 국제화된 애플리케이션을 만들 때 특히 중요합니다.

코드 예제

-- 다양한 날짜 포맷으로 변환하기
SELECT
    order_date AS 원본날짜,
    DATE_FORMAT(order_date, '%Y년 %m월 %d일') AS 한글형식,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS 표준형식,
    DATE_FORMAT(order_date, '%Y/%m/%d %H:%i:%s') AS 시간포함,
    DATE_FORMAT(order_date, '%W, %M %d, %Y') AS 영문형식,
    DATE_FORMAT(order_date, '%y%m%d') AS 짧은형식,
    DATE_FORMAT(order_date, '%H시 %i분') AS 시간만
FROM orders
WHERE order_id = 12345;

-- 월별 매출 보고서용 포맷
SELECT
    DATE_FORMAT(order_date, '%Y년 %m월') AS 주문월,
    COUNT(*) AS 주문건수,
    SUM(amount) AS 총매출
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY order_date DESC;

설명

이것이 하는 일: 이 코드는 데이터베이스에 저장된 날짜를 사용자가 보기 좋은 형식으로 변환합니다. 마치 같은 날짜를 달력에 쓸 때와 공식 문서에 쓸 때 다르게 표현하는 것처럼, 상황에 맞는 형식을 선택할 수 있습니다.

첫 번째 쿼리에서는 하나의 order_date를 여러 가지 형식으로 동시에 변환해서 보여줍니다. '%Y년 %m월 %d일' 형식은 한국 사용자에게 친숙한 형태이고, '%Y-%m-%d'는 ISO 표준 형식으로 시스템 간 데이터 교환에 적합합니다.

'%W, %M %d, %Y'는 영어권 사용자를 위한 형식으로 "Monday, January 15, 2024" 같은 결과를 만들어냅니다. 이렇게 하나의 쿼리로 여러 형식을 만들 수 있어서, 상황에 따라 필요한 컬럼을 선택해서 사용할 수 있죠.

두 번째 쿼리는 실무에서 매우 자주 사용하는 패턴입니다. 월별 집계를 할 때 GROUP BY에 DATE_FORMAT을 사용하면, 같은 년월의 데이터끼리 자동으로 묶입니다.

예를 들어 1월 1일부터 1월 31일까지의 모든 주문이 "2024년 01월"로 그룹화되어 한 줄로 집계되는 거죠. 포맷 지정자를 이해하는 것이 핵심입니다.

%Y는 4자리 연도(2024), %y는 2자리 연도(24), %m은 2자리 월(01), %c는 1자리 월(1), %d는 2자리 일(05), %e는 1자리 일(5)을 의미합니다. 시간은 %H(24시간), %h(12시간), %i(분), %s(초)로 표현하며, %W(요일 전체), %a(요일 약어), %M(월 이름 전체), %b(월 이름 약어)도 사용할 수 있습니다.

여러분이 이 함수를 사용하면 날짜 데이터를 바로 보고서나 UI에 사용할 수 있는 형태로 가져올 수 있고, 애플리케이션 코드에서 날짜 변환 로직을 작성할 필요가 없으며, 데이터베이스 쿼리 결과를 바로 엑셀이나 CSV로 내보낼 때도 보기 좋은 형식으로 저장할 수 있습니다. 특히 보고서 생성이나 배치 작업에서 날짜 형식을 통일해야 할 때 매우 유용합니다.

실전 팁

💡 GROUP BY나 ORDER BY에서 DATE_FORMAT을 사용할 때는 같은 포맷 문자열을 정확히 사용해야 합니다. SELECT와 다른 포맷을 쓰면 예상치 못한 결과가 나올 수 있습니다.

💡 퍼포먼스가 중요한 경우 DATE_FORMAT보다 YEAR(), MONTH(), DAY() 같은 단순 함수를 사용하세요. 특히 WHERE 절에서는 DATE_FORMAT을 쓰면 인덱스를 활용하지 못해 느려질 수 있습니다.

💡 다국어 지원이 필요하면 애플리케이션에서 포맷팅하는 것이 좋습니다. SQL의 DATE_FORMAT은 데이터베이스 서버의 로케일 설정에 영향을 받기 때문에 환경마다 다른 결과가 나올 수 있습니다.

💡 API 응답용으로는 ISO 8601 형식('%Y-%m-%dT%H:%i:%s')을 사용하세요. 이 형식은 국제 표준이라 대부분의 프로그래밍 언어에서 자동으로 파싱할 수 있습니다.

💡 NULL 날짜를 처리할 때는 IFNULL이나 COALESCE와 함께 사용하세요. DATE_FORMAT(NULL, ...)은 NULL을 반환하므로, IFNULL(DATE_FORMAT(date_col, '%Y-%m-%d'), '미정')처럼 기본값을 지정하면 안전합니다.

---

3. DATEDIFF로_날짜_차이_계산

시작하며

여러분이 이커머스 사이트를 운영하는데, 고객이 "언제 배송되나요?"라고 물어봅니다. 주문일이 1월 10일이고 오늘이 1월 15일이라면, "5일 전에 주문하셨네요"라고 바로 답할 수 있어야 하죠.

이런 문제는 실제 개발 현장에서 매우 자주 발생합니다. 회원 가입 후 경과일, 무료 체험 남은 기간, 연체된 일수, 프로젝트 진행 기간 등 두 날짜 사이의 차이를 계산해야 하는 경우가 정말 많거든요.

직접 계산하려면 복잡한 로직이 필요한데, 윤년이나 월별 일수 차이까지 고려하면 실수하기 쉽습니다. 바로 이럴 때 필요한 것이 DATEDIFF 함수입니다.

이 함수는 두 날짜 사이의 일수 차이를 자동으로 정확하게 계산해주므로, 여러분이 직접 복잡한 계산을 할 필요가 전혀 없습니다.

개요

간단히 말해서, DATEDIFF는 두 날짜 사이가 며칠인지 계산해주는 함수입니다. DATEDIFF(날짜1, 날짜2) 형태로 사용하며, 날짜1에서 날짜2를 뺀 일수를 반환합니다.

왜 이 함수가 필요할까요? 실무에서는 기간 계산이 비즈니스 로직의 핵심인 경우가 많습니다.

예를 들어, 대여 서비스에서 연체료를 계산하거나, 구독 서비스에서 남은 기간을 확인하거나, 마케팅 팀이 고객의 활동 주기를 분석할 때 날짜 차이 계산이 필수적입니다. 기존에는 두 날짜를 애플리케이션으로 가져와서 복잡한 계산을 했다면, 이제는 SQL 쿼리 하나로 바로 결과를 얻을 수 있습니다.

이렇게 하면 코드가 간결해지고, 데이터베이스에서 조건 필터링도 쉽게 할 수 있습니다. DATEDIFF의 핵심 특징은 첫째, 시간 부분은 무시하고 날짜만 비교한다는 점, 둘째, 결과가 양수면 날짜1이 미래이고 음수면 과거라는 점, 셋째, NULL이 포함되면 NULL을 반환한다는 점입니다.

이러한 특징들을 이해하면 다양한 비즈니스 로직을 쉽게 구현할 수 있습니다.

코드 예제

-- 주문 후 경과일 계산
SELECT
    order_id,
    order_date,
    DATEDIFF(CURRENT_DATE, order_date) AS 경과일수,
    CASE
        WHEN DATEDIFF(CURRENT_DATE, order_date) > 7 THEN '배송 지연'
        WHEN DATEDIFF(CURRENT_DATE, order_date) > 3 THEN '배송 중'
        ELSE '처리 중'
    END AS 배송상태
FROM orders
WHERE status = 'shipped';

-- 회원 가입 기간별 분류
SELECT
    user_id,
    username,
    signup_date,
    DATEDIFF(CURRENT_DATE, signup_date) AS 가입일수,
    CASE
        WHEN DATEDIFF(CURRENT_DATE, signup_date) <= 7 THEN '신규회원'
        WHEN DATEDIFF(CURRENT_DATE, signup_date) <= 30 THEN '활성회원'
        ELSE '기존회원'
    END AS 회원등급
FROM users;

-- 무료 체험 남은 기간 (가입 후 30일)
SELECT
    user_id,
    30 - DATEDIFF(CURRENT_DATE, signup_date) AS 남은일수
FROM users
WHERE DATEDIFF(CURRENT_DATE, signup_date) < 30;

설명

이것이 하는 일: 이 코드는 두 날짜 사이의 시간 간격을 일 단위로 계산하여, 주문 상태를 자동으로 분류하거나 회원 등급을 결정하는 등의 비즈니스 로직을 구현합니다. 마치 달력을 보면서 "오늘부터 생일까지 며칠 남았지?"라고 세는 것처럼, 데이터베이스가 자동으로 날짜를 계산해주는 거죠.

첫 번째 쿼리에서 DATEDIFF(CURRENT_DATE, order_date)는 오늘 날짜에서 주문 날짜를 뺀 값을 계산합니다. 예를 들어 오늘이 1월 15일이고 주문일이 1월 10일이라면 5를 반환합니다.

이 값을 CASE 문과 결합하면, 7일 이상 경과하면 "배송 지연", 3~7일이면 "배송 중", 3일 이하면 "처리 중"으로 자동 분류할 수 있습니다. 왜 이렇게 하냐면, 고객 서비스 팀이 주문 상태를 한눈에 파악할 수 있도록 하기 위함입니다.

두 번째 쿼리는 회원 관리에서 매우 유용한 패턴입니다. 가입 후 7일 이내면 "신규회원"으로 분류하여 온보딩 이메일을 보내고, 30일 이내면 "활성회원"으로 분류하여 특별 혜택을 제공하며, 그 이상이면 "기존회원"으로 관리하는 식으로 활용할 수 있습니다.

이렇게 하면 마케팅 캠페인을 회원 가입 기간에 따라 자동으로 세분화할 수 있죠. 세 번째 쿼리는 무료 체험 서비스에서 자주 사용하는 로직입니다.

30일 무료 체험을 제공한다면, 30에서 경과일을 빼면 남은 일수가 나옵니다. WHERE 절에서 경과일이 30일 미만인 사용자만 필터링하면, 아직 무료 체험 기간인 사용자 목록을 얻을 수 있습니다.

이 정보로 "무료 체험 종료 3일 전" 같은 알림을 자동으로 발송할 수 있습니다. 여러분이 이 함수를 사용하면 복잡한 날짜 계산을 단순화할 수 있고, 데이터베이스 레벨에서 필터링과 분류가 가능하며, 비즈니스 규칙을 SQL 쿼리로 직접 표현할 수 있어서 코드 유지보수가 쉬워집니다.

특히 대량의 데이터를 처리할 때 애플리케이션에서 일일이 계산하는 것보다 데이터베이스에서 한 번에 처리하는 것이 훨씬 빠릅니다.

실전 팁

💡 DATEDIFF는 시간을 무시하고 날짜만 비교합니다. 1월 15일 23:59와 1월 16일 00:01의 차이도 1일로 계산되니 주의하세요. 시간까지 정확히 비교하려면 TIMESTAMPDIFF를 사용해야 합니다.

💡 날짜 순서에 주의하세요. DATEDIFF(미래날짜, 과거날짜)는 양수, DATEDIFF(과거날짜, 미래날짜)는 음수를 반환합니다. 혼동하지 않으려면 "A에서 B를 뺀다"고 생각하면 됩니다.

💡 NULL 날짜가 포함될 수 있는 컬럼은 항상 체크하세요. DATEDIFF(date1, NULL)은 NULL을 반환하므로, COALESCE나 IFNULL로 기본값을 지정하거나 WHERE 절에서 NULL을 제외해야 합니다.

💡 인덱스 활용을 위해 WHERE 절에서는 DATEDIFF 대신 날짜 비교를 사용하세요. WHERE DATEDIFF(CURRENT_DATE, created_at) < 7보다 WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)가 인덱스를 사용할 수 있어 훨씬 빠릅니다.

💡 주 단위, 월 단위 차이가 필요하면 TIMESTAMPDIFF(WEEK, date1, date2)나 TIMESTAMPDIFF(MONTH, date1, date2)를 사용하세요. 일수를 7로 나누는 것보다 정확합니다.

---

4. DATE_ADD_DATE_SUB_날짜_연산

시작하며

여러분이 프리미엄 구독을 결제한 고객에게 "1년 후까지 사용 가능합니다"라는 만료일을 보여줘야 한다고 상상해보세요. 오늘이 2024년 1월 15일이라면 만료일은 2025년 1월 15일이 되어야 하죠.

이런 문제는 실제 개발 현장에서 끊임없이 발생합니다. 구독 만료일 계산, 배송 예정일 설정, 할인 쿠폰 유효기간 연장, 프로젝트 마감일 조정 등 현재 날짜를 기준으로 미래나 과거의 날짜를 계산해야 하는 경우가 정말 많습니다.

수동으로 계산하면 윤년이나 월말 처리에서 실수하기 쉽고요. 바로 이럴 때 필요한 것이 DATE_ADD와 DATE_SUB 함수입니다.

이 함수들을 사용하면 날짜에 일, 주, 월, 년 등을 더하거나 빼서 새로운 날짜를 정확하게 계산할 수 있습니다. 복잡한 달력 계산을 데이터베이스가 자동으로 처리해주는 거죠.

개요

간단히 말해서, DATE_ADD는 날짜에 특정 기간을 더하고, DATE_SUB는 특정 기간을 빼는 함수입니다. DATE_ADD(날짜, INTERVAL 값 단위) 형태로 사용하며, 단위는 DAY, WEEK, MONTH, YEAR 등을 사용할 수 있습니다.

왜 이 함수들이 필요할까요? 실무에서는 비즈니스 규칙에 따라 날짜를 동적으로 계산해야 합니다.

예를 들어, "무료 배송은 주문 후 3일 이내", "구독은 결제일로부터 1개월", "쿠폰은 발급일로부터 30일간 유효" 같은 규칙들을 데이터베이스에서 직접 처리할 수 있습니다. 기존에는 애플리케이션 코드에서 날짜 라이브러리를 사용해 복잡하게 계산했다면, 이제는 SQL 쿼리 하나로 간단히 해결할 수 있습니다.

특히 윤년이나 월말 처리 같은 까다로운 경우도 데이터베이스가 자동으로 올바르게 처리해줍니다. DATE_ADD/SUB의 핵심 특징은 첫째, 다양한 시간 단위(일, 주, 월, 년, 시, 분, 초)를 지원한다는 점, 둘째, 월말 처리를 자동으로 올바르게 계산한다는 점(예: 1월 31일 + 1개월 = 2월 28일), 셋째, 음수 값을 사용해 더하기 대신 빼기도 가능하다는 점입니다.

이러한 특징들이 복잡한 날짜 계산을 안전하게 만들어줍니다.

코드 예제

-- 구독 만료일 계산 (1년 후)
UPDATE subscriptions
SET expire_date = DATE_ADD(CURRENT_DATE, INTERVAL 1 YEAR)
WHERE user_id = 12345;

-- 다양한 날짜 연산 예제
SELECT
    order_date AS 주문일,
    DATE_ADD(order_date, INTERVAL 3 DAY) AS 배송예정일,
    DATE_ADD(order_date, INTERVAL 1 WEEK) AS 일주일후,
    DATE_ADD(order_date, INTERVAL 1 MONTH) AS 한달후,
    DATE_SUB(order_date, INTERVAL 7 DAY) AS 일주일전,
    DATE_ADD(order_date, INTERVAL '2:30' HOUR_MINUTE) AS 두시간반후
FROM orders
WHERE order_id = 12345;

-- 30일 이내 만료 예정 쿠폰 조회
SELECT
    coupon_code,
    expire_date,
    DATEDIFF(expire_date, CURRENT_DATE) AS 남은일수
FROM coupons
WHERE expire_date BETWEEN CURRENT_DATE
    AND DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY expire_date;

-- 지난 달 주문 통계
SELECT COUNT(*) AS 지난달주문수
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
  AND order_date < CURRENT_DATE;

설명

이것이 하는 일: 이 코드는 현재 날짜를 기준으로 미래나 과거의 날짜를 자동으로 계산하여, 구독 만료일을 설정하거나 특정 기간의 데이터를 조회하는 등의 작업을 수행합니다. 마치 달력을 펼쳐놓고 "오늘부터 3일 후는 언제지?"라고 세는 것처럼, 데이터베이스가 정확하게 날짜를 계산해주는 거죠.

첫 번째 UPDATE 쿼리에서 DATE_ADD(CURRENT_DATE, INTERVAL 1 YEAR)는 오늘 날짜에 정확히 1년을 더합니다. 예를 들어 2024년 1월 15일이라면 2025년 1월 15일을 반환하고, 윤년인 2024년 2월 29일이라면 2025년 2월 28일을 반환합니다(2025년은 윤년이 아니므로).

이렇게 복잡한 달력 규칙을 데이터베이스가 자동으로 처리해주므로, 구독 만료일을 정확하게 설정할 수 있습니다. 두 번째 SELECT 쿼리는 다양한 시간 단위의 날짜 연산을 보여줍니다.

INTERVAL 3 DAY는 3일 후, INTERVAL 1 WEEK는 7일 후, INTERVAL 1 MONTH는 한 달 후를 의미합니다. 재미있는 점은 DATE_SUB를 사용하거나 음수 INTERVAL을 사용해서 과거 날짜도 계산할 수 있다는 것입니다.

INTERVAL '2:30' HOUR_MINUTE처럼 시간과 분을 조합하는 것도 가능하여, "주문 후 2시간 30분 뒤 픽업 가능" 같은 로직도 구현할 수 있죠. 세 번째 쿼리는 실무에서 자주 사용하는 "곧 만료될 항목 찾기" 패턴입니다.

BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY)를 사용하면 오늘부터 30일 이내에 만료되는 쿠폰을 모두 찾을 수 있습니다. 이 정보로 "쿠폰 만료 임박" 알림을 자동으로 발송하거나, 관리자 대시보드에 경고를 표시할 수 있습니다.

네 번째 쿼리는 기간별 통계를 낼 때 핵심적인 패턴입니다. DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)는 정확히 한 달 전 날짜를 반환하므로, 그 날짜 이후부터 오늘까지의 주문을 집계하면 지난 달 통계가 나옵니다.

이 방법은 "최근 7일", "최근 3개월" 같은 동적 기간 조회에도 똑같이 적용할 수 있습니다. 여러분이 이 함수들을 사용하면 복잡한 날짜 계산을 안전하게 처리할 수 있고, 윤년이나 월말 같은 예외 상황도 자동으로 올바르게 처리되며, 동적 기간 조회가 가능해져 유연한 데이터 분석을 할 수 있습니다.

특히 정기 구독이나 멤버십 서비스에서 만료일 관리는 비즈니스의 핵심이므로, 이 함수들을 정확히 이해하는 것이 매우 중요합니다.

실전 팁

💡 월 단위 계산 시 월말 처리를 주의하세요. 1월 31일에 1개월을 더하면 2월 28일(또는 29일)이 됩니다. 예상과 다를 수 있으니 테스트를 꼭 해보세요.

💡 DATE_ADD 대신 간단한 + 연산자도 사용할 수 있습니다. DATE_ADD(date, INTERVAL 3 DAY)는 date + INTERVAL 3 DAY로 쓸 수 있어 코드가 더 간결합니다.

💡 여러 단위를 조합할 수 있습니다. INTERVAL '1-2' YEAR_MONTH는 1년 2개월, INTERVAL '3 12:30:00' DAY_SECOND는 3일 12시간 30분을 의미합니다.

💡 WHERE 절에서 날짜 범위 조회 시 인덱스를 활용하려면 컬럼을 함수로 감싸지 말고, 비교값을 계산하세요. WHERE DATE_ADD(created_at, INTERVAL 7 DAY) < CURRENT_DATE보다 WHERE created_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)가 인덱스를 사용해 빠릅니다.

💡 NULL 날짜에 연산을 하면 NULL이 반환됩니다. 항상 COALESCE나 IFNULL로 기본값을 설정하거나, WHERE 절에서 NULL을 제외하세요.

---

5. EXTRACT로_날짜_부분_추출

시작하며

여러분이 매출 데이터를 분석하는데, "이번 달 매출은 얼마지?", "월요일에 주문이 가장 많나?", "몇 시에 트래픽이 몰리지?" 같은 질문에 답해야 한다고 상상해보세요. 이런 질문들의 공통점은 날짜에서 특정 부분만 추출해야 한다는 것입니다.

이런 문제는 실제 개발 현장에서 데이터 분석과 리포팅을 할 때 항상 발생합니다. 완전한 날짜/시간 데이터에서 년, 월, 일, 시간, 요일 같은 특정 요소만 뽑아내야 하는 경우가 많거든요.

이를 통해 같은 월끼리, 같은 요일끼리 그룹화해서 패턴을 분석할 수 있습니다. 바로 이럴 때 필요한 것이 EXTRACT 함수와 관련 함수들(YEAR, MONTH, DAY, HOUR 등)입니다.

이 함수들을 사용하면 날짜 데이터에서 원하는 부분만 정확하게 추출하여, 강력한 데이터 분석이 가능해집니다.

개요

간단히 말해서, EXTRACT는 날짜/시간 데이터에서 특정 부분(년, 월, 일, 시, 분, 초, 요일 등)을 숫자로 추출하는 함수입니다. EXTRACT(부분 FROM 날짜) 형태로 사용하며, YEAR(), MONTH(), DAY() 같은 전용 함수도 있습니다.

왜 이 함수들이 필요할까요? 실무에서는 시간대별, 요일별, 월별 분석이 매우 중요합니다.

예를 들어, 전자상거래에서는 "몇 월에 매출이 높은지", "무슨 요일에 주문이 많은지", "몇 시에 서버 부하가 높은지"를 파악해야 마케팅 전략과 인프라 운영을 최적화할 수 있습니다. 기존에는 전체 날짜를 애플리케이션으로 가져와서 파싱했다면, 이제는 SQL 쿼리에서 바로 필요한 부분만 추출하고 그룹화할 수 있습니다.

이렇게 하면 데이터베이스 레벨에서 집계가 이루어지므로 훨씬 효율적입니다. EXTRACT의 핵심 특징은 첫째, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 등 다양한 부분을 추출할 수 있다는 점, 둘째, DAYOFWEEK, WEEK, QUARTER 같은 특수한 값도 얻을 수 있다는 점, 셋째, 추출된 값은 정수이므로 산술 연산이나 비교가 쉽다는 점입니다.

이러한 특징들이 복잡한 시계열 분석을 가능하게 만듭니다.

코드 예제

-- 날짜에서 다양한 부분 추출
SELECT
    order_date,
    YEAR(order_date) AS 년도,
    MONTH(order_date) AS 월,
    DAY(order_date) AS 일,
    HOUR(order_date) AS 시간,
    DAYOFWEEK(order_date) AS 요일번호,  -- 1=일요일, 7=토요일
    DAYNAME(order_date) AS 요일이름,
    QUARTER(order_date) AS 분기,
    WEEK(order_date) AS 주차
FROM orders
WHERE order_id = 12345;

-- 월별 매출 집계
SELECT
    YEAR(order_date) AS 년도,
    MONTH(order_date) AS 월,
    COUNT(*) AS 주문수,
    SUM(amount) AS 총매출
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY 년도 DESC, 월 DESC;

-- 요일별 주문 패턴 분석
SELECT
    DAYNAME(order_date) AS 요일,
    COUNT(*) AS 주문건수,
    AVG(amount) AS 평균주문금액
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY DAYOFWEEK(order_date), DAYNAME(order_date)
ORDER BY DAYOFWEEK(order_date);

-- 시간대별 트래픽 분석
SELECT
    HOUR(created_at) AS 시간대,
    COUNT(*) AS 방문수
FROM page_views
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY HOUR(created_at)
ORDER BY 시간대;

설명

이것이 하는 일: 이 코드는 날짜/시간 데이터를 년, 월, 일, 시간, 요일 같은 개별 요소로 분해하여, 시간대별 패턴을 분석하거나 특정 기간으로 데이터를 그룹화합니다. 마치 시계를 보면서 "지금 몇 시야?"라고 시간만 확인하는 것처럼, 날짜에서 필요한 부분만 뽑아내는 거죠.

첫 번째 SELECT 쿼리는 하나의 날짜 데이터에서 추출할 수 있는 다양한 정보를 보여줍니다. YEAR()는 2024, MONTH()는 112, DAY()는 131, HOUR()는 0~23 범위의 정수를 반환합니다.

DAYOFWEEK()는 1(일요일)부터 7(토요일)까지의 숫자를 반환하고, DAYNAME()은 'Monday', 'Tuesday' 같은 요일 이름을 반환합니다. QUARTER()는 1~4 분기를, WEEK()는 1년 중 몇 번째 주인지를 반환하여, 다양한 기준으로 데이터를 분류할 수 있습니다.

두 번째 쿼리는 실무에서 가장 많이 사용하는 패턴인 월별 집계입니다. GROUP BY에 YEAR(order_date), MONTH(order_date)를 사용하면, 같은 년도와 월의 주문들이 자동으로 그룹화됩니다.

예를 들어 2024년 1월의 모든 주문이 한 그룹이 되어, 그 달의 총 주문 수와 매출이 한 줄로 집계됩니다. 이렇게 하면 월별 매출 추이를 쉽게 파악할 수 있어서, "어느 달에 프로모션을 할지" 같은 전략을 세울 수 있죠.

세 번째 쿼리는 요일별 패턴 분석으로, 비즈니스 인사이트를 얻는 데 매우 유용합니다. DAYNAME()으로 요일을 추출하고 GROUP BY로 그룹화하면, "월요일에는 평균 10만원어치 주문", "토요일에는 주문이 가장 많다" 같은 패턴을 발견할 수 있습니다.

이 정보로 요일별로 다른 마케팅 전략을 세우거나, 재고를 미리 준비하거나, 고객 서비스 인력을 조정할 수 있습니다. GROUP BY에 DAYOFWEEK도 함께 사용하는 이유는 정렬을 일요일부터 토요일 순서로 하기 위함입니다.

네 번째 쿼리는 시간대별 분석으로, 서비스 운영 최적화에 핵심적입니다. HOUR()로 시간을 추출하면 0시부터 23시까지의 시간대별로 방문 수를 집계할 수 있습니다.

예를 들어 "오후 8시에 트래픽이 가장 많다"는 것을 알면, 그 시간대에 서버 용량을 늘리거나 배치 작업은 새벽에 실행하도록 스케줄링할 수 있습니다. 여러분이 이 함수들을 사용하면 시계열 데이터의 패턴을 쉽게 발견할 수 있고, 비즈니스 의사결정을 위한 인사이트를 데이터에서 직접 추출할 수 있으며, 복잡한 보고서를 SQL 쿼리만으로 생성할 수 있습니다.

특히 BI 도구나 대시보드에서 사용할 데이터를 준비할 때, 이런 함수들로 미리 집계해두면 성능이 크게 향상됩니다.

실전 팁

💡 요일 번호는 데이터베이스마다 다를 수 있습니다. MySQL은 일요일이 1이지만, PostgreSQL은 월요일이 1입니다. 이식성을 위해 DAYNAME()을 사용하거나 명시적으로 변환하세요.

💡 GROUP BY에서 날짜 함수를 사용하면 인덱스를 활용하지 못합니다. 대량 데이터를 자주 집계한다면, 년/월/일을 별도 컬럼으로 저장하고 인덱스를 생성하는 것이 성능상 유리합니다.

💡 분기별 집계에는 QUARTER() 함수가 편리합니다. 13월은 1분기, 46월은 2분기 식으로 자동 계산되어, 재무 보고서 작성 시 매우 유용합니다.

💡 EXTRACT는 표준 SQL 문법이라 이식성이 좋습니다. EXTRACT(YEAR FROM date)는 YEAR(date)와 같지만, PostgreSQL 같은 다른 데이터베이스로 이전할 때 수정이 필요 없습니다.

💡 시간대별 분석 시 00시~06시는 활동이 적으므로, HAVING COUNT(*) > 임계값 조건을 추가하면 의미 있는 데이터만 볼 수 있습니다.

---

6. 타임존_처리하기

시작하며

여러분이 글로벌 서비스를 운영하는데, 서울의 고객이 "2024년 1월 15일 오후 3시에 주문했어요"라고 하고, 뉴욕의 고객도 "2024년 1월 15일 오후 3시에 주문했어요"라고 한다면 어떻게 처리해야 할까요? 같은 시간처럼 보이지만, 실제로는 14시간 차이가 나는 완전히 다른 시간입니다.

이런 문제는 국제적으로 서비스하는 모든 애플리케이션에서 발생합니다. 서버는 UTC(협정 세계시)로 시간을 저장하지만, 사용자에게는 각자의 타임존에 맞는 시간을 보여줘야 하기 때문이죠.

잘못 처리하면 "주문 시간이 이상해요", "배송 예정일이 하루 차이나요" 같은 심각한 문제가 발생합니다. 바로 이럴 때 필요한 것이 CONVERT_TZ 함수와 타임존 관련 함수들입니다.

이 함수들을 사용하면 서로 다른 타임존 간의 시간 변환을 정확하게 처리하여, 전 세계 사용자에게 올바른 시간 정보를 제공할 수 있습니다.

개요

간단히 말해서, CONVERT_TZ는 한 타임존의 시간을 다른 타임존으로 변환하는 함수입니다. CONVERT_TZ(날짜시간, 원본타임존, 대상타임존) 형태로 사용하며, UTC_TIMESTAMP()는 현재 UTC 시간을 반환합니다.

왜 이 함수들이 필요할까요? 실무에서는 데이터를 저장할 때는 UTC를 사용하고, 사용자에게 보여줄 때는 로컬 타임존으로 변환하는 것이 베스트 프랙티스입니다.

예를 들어, 서울(+09:00)과 뉴욕(-05:00)의 고객이 같은 서비스를 사용하더라도, 각자 자신의 시간대로 정보를 볼 수 있어야 합니다. 기존에는 타임존 변환을 애플리케이션 코드에서 복잡하게 처리했다면, 이제는 SQL 쿼리에서 바로 변환할 수 있습니다.

이렇게 하면 데이터베이스에서 이미 변환된 시간을 받으므로, 클라이언트 측 로직이 단순해집니다. 타임존 함수들의 핵심 특징은 첫째, 일광 절약 시간(DST)도 자동으로 처리한다는 점, 둘째, 타임존 이름(예: 'Asia/Seoul')이나 오프셋(예: '+09:00')을 모두 지원한다는 점, 셋째, UTC 기준으로 저장하면 타임존 문제를 근본적으로 해결할 수 있다는 점입니다.

이러한 특징들이 글로벌 서비스의 시간 데이터를 일관되게 관리할 수 있게 만듭니다.

코드 예제

-- UTC 시간으로 저장하기 (권장 방식)
INSERT INTO orders (user_id, order_date_utc)
VALUES (12345, UTC_TIMESTAMP());

-- UTC 시간을 다른 타임존으로 변환
SELECT
    order_id,
    order_date_utc AS UTC시간,
    CONVERT_TZ(order_date_utc, '+00:00', '+09:00') AS 서울시간,
    CONVERT_TZ(order_date_utc, '+00:00', '-05:00') AS 뉴욕시간,
    CONVERT_TZ(order_date_utc, '+00:00', '+00:00') AS 런던시간
FROM orders
WHERE order_id = 12345;

-- 사용자의 타임존에 맞춰 주문 조회
SELECT
    order_id,
    CONVERT_TZ(order_date_utc, '+00:00', '+09:00') AS 주문시간
FROM orders
WHERE user_id = 12345
  AND DATE(CONVERT_TZ(order_date_utc, '+00:00', '+09:00')) = '2024-01-15'
ORDER BY order_date_utc DESC;

-- 현재 시스템 타임존 확인
SELECT
    NOW() AS 시스템현재시간,
    UTC_TIMESTAMP() AS UTC현재시간,
    @@system_time_zone AS 시스템타임존,
    @@session.time_zone AS 세션타임존;

설명

이것이 하는 일: 이 코드는 전 세계 서로 다른 타임존에 있는 사용자들에게 정확한 시간 정보를 제공하기 위해, UTC로 저장된 시간을 각 지역의 로컬 시간으로 변환합니다. 마치 국제 전화를 할 때 상대방의 시간대를 고려하는 것처럼, 데이터베이스가 자동으로 시간대를 변환해주는 거죠.

첫 번째 INSERT 쿼리에서 UTC_TIMESTAMP()를 사용하는 것이 핵심입니다. NOW() 대신 UTC_TIMESTAMP()를 사용하면 서버가 어느 타임존에 있든 관계없이 항상 UTC 기준 시간이 저장됩니다.

예를 들어 서울에 있는 서버에서 2024년 1월 15일 오후 3시(15:00)에 실행하면, UTC로는 오전 6시(06:00)가 저장됩니다. 이렇게 하면 나중에 어느 타임존으로든 정확하게 변환할 수 있는 기준점이 생기는 거죠.

두 번째 SELECT 쿼리는 하나의 UTC 시간을 여러 타임존으로 동시에 변환하는 방법을 보여줍니다. CONVERT_TZ(order_date_utc, '+00:00', '+09:00')는 "UTC(+00:00)에서 한국(+09:00)으로 변환"을 의미합니다.

만약 UTC로 06:00이 저장되어 있다면, 서울 시간으로는 15:00, 뉴욕 시간으로는 01:00으로 변환됩니다. 같은 주문 시간이지만 각 지역 사용자에게는 자신의 시간대로 보이는 거죠.

세 번째 쿼리는 실무에서 자주 마주치는 함정을 보여줍니다. WHERE 절에서 날짜를 비교할 때 반드시 UTC 시간을 사용자 타임존으로 변환한 후 비교해야 합니다.

만약 변환하지 않고 UTC 시간으로 '2024-01-15'를 비교하면, 한국 사용자 입장에서는 하루 전이나 후의 주문까지 포함될 수 있습니다. 한국 시간 1월 15일은 UTC로 1월 14일 15:00부터 1월 15일 15:00까지이기 때문이죠.

네 번째 쿼리는 디버깅할 때 매우 유용한 정보를 보여줍니다. NOW()와 UTC_TIMESTAMP()의 차이를 보면 현재 서버가 어느 타임존으로 설정되어 있는지 알 수 있고, @@system_time_zone과 @@session.time_zone을 확인하면 데이터베이스 설정이 올바른지 검증할 수 있습니다.

만약 NOW()와 UTC_TIMESTAMP()가 같다면 서버가 UTC로 설정되어 있다는 뜻이고, 9시간 차이가 난다면 Asia/Seoul로 설정되어 있다는 뜻입니다. 여러분이 타임존 함수들을 올바르게 사용하면 전 세계 사용자에게 정확한 시간을 보여줄 수 있고, 서버를 다른 지역으로 이전해도 시간 데이터가 깨지지 않으며, 일광 절약 시간 변경 같은 복잡한 상황도 자동으로 처리됩니다.

특히 글로벌 서비스나 여러 지역에 서버를 두는 분산 시스템에서는 타임존 관리가 데이터 정합성의 핵심입니다.

실전 팁

💡 항상 UTC로 저장하고 표시할 때만 로컬 타임존으로 변환하세요. 이것이 타임존 문제를 피하는 가장 확실한 방법입니다. DATETIME보다 TIMESTAMP 타입을 사용하면 MySQL이 자동으로 타임존 변환을 처리합니다.

💡 CONVERT_TZ 함수를 사용하려면 MySQL 타임존 테이블이 로드되어 있어야 합니다. mysql_tzinfo_to_sql 명령으로 시스템의 타임존 데이터를 MySQL에 로드하세요. 그렇지 않으면 '+09:00' 같은 오프셋만 사용 가능합니다.

💡 애플리케이션에서 사용자별 타임존을 저장하고, 쿼리 시 해당 값으로 변환하세요. 예를 들어 users 테이블에 timezone 컬럼을 두고, CONVERT_TZ(..., '+00:00', u.timezone) 형태로 사용하면 개인화된 시간 표시가 가능합니다.

💡 날짜 범위 검색 시 타임존 변환 순서에 주의하세요. 사용자가 "2024-01-15" 데이터를 요청하면, 먼저 로컬 날짜를 UTC 범위로 변환한 후 WHERE order_date_utc BETWEEN ... 형태로 검색해야 정확합니다.

💡 프론트엔드와 백엔드 간 시간 전달 시 ISO 8601 형식(2024-01-15T15:30:00Z)을 사용하세요. Z는 UTC를 의미하며, 대부분의 언어와 라이브러리에서 자동으로 파싱할 수 있어 타임존 혼란을 방지합니다.

---