SQL 날짜/시간 함수 — 코딩테스트 필수 정리

💡 코테에서 가장 자주 나오는 날짜/시간 함수를 완벽 정리한다.

---

🤔 왜 중요해?

코딩테스트 문제 유형:

• "2024년 1월에 가입한 유저"
• "최근 7일간 주문"
• "월별 매출 집계"
• "요일별 트래픽"

→ 날짜 함수 모르면 못 푸는 문제 많음!

---

📊 예제 테이블

[orders]
| order_id | user_id | amount | order_date | order_time |
|----------|---------|--------|------------|------------|
| 1 | 101 | 150000 | 2024-01-15 | 2024-01-15 14:30:00 |
| 2 | 102 | 50000 | 2024-01-20 | 2024-01-20 09:15:00 |
| 3 | 101 | 200000 | 2024-02-05 | 2024-02-05 18:45:00 |
| 4 | 103 | 80000 | 2024-02-10 | 2024-02-10 11:00:00 |

---

🗓️ DB별 문법 차이

기능	MySQL	PostgreSQL	BigQuery
현재 날짜	CURDATE()	CURRENT_DATE	CURRENT_DATE()
현재 시간	NOW()	NOW()	CURRENT_TIMESTAMP()
날짜 포맷	DATE_FORMAT()	TO_CHAR()	FORMAT_DATE()
날짜 추출	YEAR(), MONTH()	EXTRACT()	EXTRACT()
날짜 연산	DATE_ADD()	+ INTERVAL	DATE_ADD()

---

1️⃣ 현재 날짜/시간

MySQL

SELECT 
    CURDATE() AS today,           -- 2024-01-15
    NOW() AS now,                 -- 2024-01-15 14:30:00
    CURRENT_TIMESTAMP AS ts;      -- 2024-01-15 14:30:00

PostgreSQL

SELECT 
    CURRENT_DATE AS today,
    NOW() AS now,
    CURRENT_TIMESTAMP AS ts;

BigQuery

SELECT 
    CURRENT_DATE() AS today,
    CURRENT_TIMESTAMP() AS now;

---

2️⃣ 날짜 부분 추출

MySQL - 개별 함수

SELECT 
    YEAR(order_date) AS year,       -- 2024
    MONTH(order_date) AS month,     -- 1
    DAY(order_date) AS day,         -- 15
    DAYOFWEEK(order_date) AS dow,   -- 1(일)~7(토)
    WEEKDAY(order_date) AS weekday, -- 0(월)~6(일)
    HOUR(order_time) AS hour,       -- 14
    MINUTE(order_time) AS minute    -- 30
FROM orders;

MySQL - EXTRACT

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;

BigQuery - EXTRACT

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAYOFWEEK FROM order_date) AS dow  -- 1(일)~7(토)
FROM orders;

---

3️⃣ 날짜 포맷팅

MySQL - DATE_FORMAT

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS ym,      -- 2024-01
    DATE_FORMAT(order_date, '%Y-%m-%d') AS ymd,  -- 2024-01-15
    DATE_FORMAT(order_date, '%Y년 %m월') AS kr,  -- 2024년 01월
    DATE_FORMAT(order_time, '%H:%i') AS time     -- 14:30
FROM orders;

주요 포맷 코드:
| 코드 | 의미 | 예시 |
|------|------|------|
| %Y | 연도 4자리 | 2024 |
| %y | 연도 2자리 | 24 |
| %m | 월 2자리 | 01, 12 |
| %d | 일 2자리 | 01, 31 |
| %H | 시간 24시 | 00~23 |
| %i | 분 | 00~59 |
| %s | 초 | 00~59 |
| %W | 요일 이름 | Monday |
| %w | 요일 숫자 | 0(일)~6(토) |

PostgreSQL - TO_CHAR

SELECT 
    TO_CHAR(order_date, 'YYYY-MM') AS ym,
    TO_CHAR(order_date, 'YYYY-MM-DD') AS ymd
FROM orders;

BigQuery - FORMAT_DATE

SELECT 
    FORMAT_DATE('%Y-%m', order_date) AS ym,
    FORMAT_DATE('%Y-%m-%d', order_date) AS ymd
FROM orders;

---

4️⃣ 날짜 연산

MySQL

SELECT 
    -- 더하기
    DATE_ADD(order_date, INTERVAL 7 DAY) AS plus_7days,
    DATE_ADD(order_date, INTERVAL 1 MONTH) AS plus_1month,
    DATE_ADD(order_date, INTERVAL 1 YEAR) AS plus_1year,
    
    -- 빼기
    DATE_SUB(order_date, INTERVAL 7 DAY) AS minus_7days,
    
    -- 또는 직접 연산
    order_date + INTERVAL 7 DAY AS plus_7days2
FROM orders;

날짜 차이 계산

SELECT 
    DATEDIFF('2024-01-20', '2024-01-15') AS diff_days,  -- 5
    TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-03-15') AS diff_months,  -- 2
    TIMESTAMPDIFF(HOUR, '2024-01-15 10:00:00', '2024-01-15 14:30:00') AS diff_hours  -- 4
FROM dual;

BigQuery

SELECT 
    DATE_ADD(order_date, INTERVAL 7 DAY) AS plus_7days,
    DATE_SUB(order_date, INTERVAL 1 MONTH) AS minus_1month,
    DATE_DIFF(date1, date2, DAY) AS diff_days
FROM orders;

---

5️⃣ 날짜 범위 필터링

특정 기간

-- 2024년 1월 데이터
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2024-02-01';

-- 또는 BETWEEN (주의: 끝 날짜 포함)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

최근 N일

-- 최근 7일
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 최근 30일
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

특정 연/월 추출

-- 2024년 데이터
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;

-- 1월 데이터 (모든 연도)
SELECT * FROM orders
WHERE MONTH(order_date) = 1;

-- 2024년 1월
SELECT * FROM orders
WHERE YEAR(order_date) = 2024
  AND MONTH(order_date) = 1;

---

6️⃣ 날짜별 집계

일별

SELECT 
    order_date,
    COUNT(*) AS order_count,
    SUM(amount) AS daily_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;

월별

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

요일별

SELECT 
    DAYOFWEEK(order_date) AS dow,
    CASE DAYOFWEEK(order_date)
        WHEN 1 THEN '일'
        WHEN 2 THEN '월'
        WHEN 3 THEN '화'
        WHEN 4 THEN '수'
        WHEN 5 THEN '목'
        WHEN 6 THEN '금'
        WHEN 7 THEN '토'
    END AS day_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY DAYOFWEEK(order_date)
ORDER BY dow;

시간대별

SELECT 
    HOUR(order_time) AS hour,
    COUNT(*) AS order_count
FROM orders
GROUP BY HOUR(order_time)
ORDER BY hour;

---

7️⃣ 날짜 변환

문자열 → 날짜

-- MySQL
SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d');
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');

-- BigQuery
SELECT PARSE_DATE('%Y-%m-%d', '2024-01-15');

날짜 → 문자열

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d');

-- BigQuery  
SELECT FORMAT_DATE('%Y-%m-%d', order_date);

TIMESTAMP ↔ DATE

-- TIMESTAMP → DATE
SELECT DATE(order_time) AS order_date;

-- DATE → TIMESTAMP
SELECT TIMESTAMP(order_date) AS order_ts;

---

🎯 실전 문제

문제 1: 월별 집계

2024년 월별 주문 건수와 매출을 구하세요.

정답 보기

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS revenue
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

---

문제 2: 최근 7일

최근 7일간 일별 주문 건수를 구하세요.

정답 보기

SELECT 
    order_date,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY order_date
ORDER BY order_date;

---

문제 3: 가입 후 첫 주문

각 유저의 가입일과 첫 주문일, 그리고 그 사이 일수를 구하세요.

정답 보기

SELECT 
    u.user_id,
    u.created_at AS signup_date,
    MIN(o.order_date) AS first_order_date,
    DATEDIFF(MIN(o.order_date), u.created_at) AS days_to_first_order
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.created_at;

---

💡 자주 하는 실수

1. BETWEEN의 경계값

-- ❌ 2024-01-31 23:59:59 데이터 누락 가능
WHERE order_time BETWEEN '2024-01-01' AND '2024-01-31'

-- ✅ 다음 달 첫날 미만으로
WHERE order_time >= '2024-01-01' AND order_time < '2024-02-01'

2. 시간대(Timezone) 고려

-- UTC로 저장된 데이터를 KST로 변환
SELECT CONVERT_TZ(order_time, 'UTC', 'Asia/Seoul') AS kst_time;

3. 문자열 비교 주의

-- ❌ 문자열 비교 (예상과 다른 결과)
WHERE order_date = '2024-1-5'

-- ✅ 정확한 포맷
WHERE order_date = '2024-01-05'

---

📝 핵심 정리

[추출]
YEAR(), MONTH(), DAY(), HOUR()
EXTRACT(part FROM date)

[포맷]
MySQL: DATE_FORMAT(date, '%Y-%m-%d')
BigQuery: FORMAT_DATE('%Y-%m-%d', date)

[연산]
DATE_ADD(date, INTERVAL n DAY/MONTH/YEAR)
DATE_SUB(date, INTERVAL n DAY)
DATEDIFF(date1, date2)

[필터링]
WHERE date >= '시작' AND date < '끝'
WHERE YEAR(date) = 2024 AND MONTH(date) = 1

---

🔗 다음 포스트

[SQL 시리즈 #7] 문자열 함수 — 데이터 정제의 기본

---

📝 날짜 함수는 코딩테스트의 단골 주제다.
DATE_FORMAT과 날짜 연산만 확실히 알아도 대부분의 문제에 대응 가능!