정현석

개인 저장공간입니다.
Since 2021.10

3 분 소요

MySQL에서 날짜와 시간을 다루는 함수는 로그 분석, 기간별 집계, 리텐션 계산의 기반이다. 이 글에서는 날짜 데이터 타입의 차이(DATE vs DATETIME vs TIMESTAMP)와 날짜 비교·범위 추출·CTE(WITH) 활용 패턴을 정리한다.

1. MySQL의 날짜 관련 데이터 타입

타입 저장 형식 특징 예시
DATE YYYY-MM-DD 날짜만 저장 (시간 없음) '2023-09-01'
DATETIME YYYY-MM-DD HH:MM:SS 날짜+시간 저장, 타임존 변환 없이 입력값 그대로 보존 '2023-09-01 15:30:00'
TIMESTAMP YYYY-MM-DD HH:MM:SS UTC 기준 저장, 세션 타임존에 따라 조회 시 변환 (1970~2038 범위) '2023-09-01 15:30:00'
TIME HH:MM:SS 시간만 저장 (음수·초과 시간 허용) '15:30:00'
YEAR YYYY 연도만 저장 (1901~2155) '2023'

실무 판단 기준: 생성 시각 이력은 타임존 이슈가 있는 TIMESTAMP보다 DATETIME(UTC 저장 규약)을, 서버 로컬 기준 만료 계산이 필요한 경우엔 TIMESTAMP를 선택하는 것이 일반적이다.1

2. MySQL의 날짜 관련 함수

  • MySQL은 날짜와 시간을 다루는 함수를 다수 제공한다. 자주 쓰이는 함수는 다음과 같다.2

1) NOW()

  • 현재 날짜와 시간을 반환한다 (형식: DATETIME).
  • 예시:
SELECT NOW();
# 출력: 2023-09-01 15:30:00

2) CURDATE()

  • 현재 날짜만 반환한다 (DATE 형식).
  • 예시:
SELECT CURDATE();
# 출력: 2023-09-01

3) CURTIME()

  • 현재 시간만 반환한다 (TIME 형식).
  • 예시:
SELECT CURTIME();
# 출력: 15:30:00

4) DATE()

  • DATETIME 또는 TIMESTAMP 값에서 날짜만 추출한다.
  • 예시:
SELECT DATE(NOW());
# 출력: 2023-09-01

5) YEAR(), MONTH(), DAY()

  • 날짜에서 연도, 월, 일을 각각 추출한다.
  • 예시:
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
# 출력: 2023 9 1

6) DATE_ADD()와 DATE_SUB()

  • 날짜에 기간을 더하거나 빼는 함수다. 일, 월, 연도 등의 단위를 지정해 사용한다.

DATE_ADD()

  • 지정된 날짜에 특정 기간을 더한다.
  • 예시: 10일 후 날짜를 계산
SELECT DATE_ADD('2023-09-01', INTERVAL 10 DAY);
# 출력: 2023-09-11

DATE_SUB()

  • 지정된 날짜에서 특정 기간을 뺀다.
  • 예시: 2개월 전 날짜를 계산
SELECT DATE_SUB('2023-09-01', INTERVAL 2 MONTH);
# 출력: 2023-07-01

7) DATEDIFF()

  • 두 날짜 간의 일 수 차이를 계산한다.
  • 예시:
SELECT DATEDIFF('2023-09-10', '2023-09-01');
# 출력: 9

8) TIMESTAMPDIFF()

  • 두 날짜/시간 간의 차이를 특정 단위(연도, 월, 일, 시간 등)로 반환한다.
  • 예시: 시간 차이 계산
SELECT TIMESTAMPDIFF(HOUR, '2023-09-01 10:00:00', '2023-09-01 15:00:00');
# 출력: 5

9) STR_TO_DATE()

  • 문자열을 날짜로 변환한다. 다양한 형식의 날짜 문자열을 DATE 형식으로 바꿀 때 쓴다.
  • 예시:
SELECT STR_TO_DATE('01,09,2023', '%d,%m,%Y');
# 출력: 2023-09-01

10) DATE_FORMAT()

  • 날짜를 지정된 형식으로 변환한다. DATE, DATETIME 값을 원하는 형식으로 표시할 때 쓴다.
  • 예시:
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
# 출력: 2023-09-01 15:30:00

3. 날짜 비교와 범위 선택

1) 날짜 비교

MySQL에서는 날짜를 비교할 때 일반적인 비교 연산자 (=, <, >, <=, >=, !=)를 사용할 수 있다.

SELECT *
FROM Orders
WHERE order_date = '2023-09-01';
# 위 쿼리는 **`order_date`**가 `'2023-09-01'`과 일치하는 모든 행을 반환한다.

2) 날짜 범위 선택 (BETWEEN)

  • BETWEEN 연산자를 사용하면 두 날짜 사이에 있는 데이터를 쉽게 조회할 수 있다.
SELECT *
FROM Orders
WHERE order_date BETWEEN '2023-08-01' AND '2023-08-31';
# 이 쿼리는 **`order_date`가 '2023-08-01'과 '2023-08-31' 사이**에 있는 주문을 반환한다. **`BETWEEN`은 경계 값을 포함**하므로 두 날짜의 00:00:00도 결과에 들어간다.[^3]

3) 날짜 필터링을 위한 서브쿼리

  • 조건에 맞는 날짜 범위를 서브쿼리로 계산해 넘길 수도 있다.
# 이 쿼리는 **최근 7일 내의 주문**을 조회한다. `CURDATE()`와 **`INTERVAL`**로 조건을 동적으로 설정한다.
SELECT *
FROM Orders
WHERE order_date >= (SELECT CURDATE() - INTERVAL 7 DAY);

4) 범위 조건의 경계와 주의 사항

  • order_date가 DATETIME이면 위 BETWEEN 예제의 상한 '2023-08-31'은 2023-08-31 00:00:00으로 해석되므로, 그날 낮에 기록된 행은 결과에서 빠진다. 경계일 하루치를 통째로 포함하려면 order_date >= '2023-08-01' AND order_date < '2023-09-01'처럼 반개구간으로 쓴다.3
  • TIMESTAMP 컬럼에 문자열이나 NOW()를 비교하면 세션 타임존을 기준으로 변환되므로, 서버의 타임존 설정이 바뀌면 같은 데이터라도 조건의 결과가 달라질 수 있다.
  • TIMESTAMP는 1장 표의 범위(1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC)를 벗어난 값을 저장할 수 없으므로, 2038년 이후 만료 시각을 담을 컬럼에는 DATETIME을 쓴다.

4. 날짜 연산과 추가 활용

1) 날짜와 시간의 더하기 및 빼기

  • DATE_ADD()와 DATE_SUB() 함수는 날짜 연산을 쉽게 처리할 수 있다. 특정 날짜에 일, 월, 연도 등을 더하거나 빼서 계산한다.
SELECT DATE_ADD(CURDATE(), INTERVAL 10 DAY) AS ten_days_later,
       DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS five_days_ago;
/*
 출력:

| ten_days_later | five_days_ago |
|----------------|---------------|
| 2023-09-11     | 2023-08-27    |
 */

2) 시간 차이 계산

  • TIMEDIFF() 함수는 두 시간 사이의 차이를 계산할 때 사용된다.
SELECT TIMEDIFF('2023-09-01 15:30:00', '2023-09-01 10:00:00') AS time_difference;
# 출력: 05:30:00

3) 특정 형식의 날짜 추출 및 변환

  • DATE_FORMAT()을 통해 날짜의 출력 형식을 바꿀 수 있다. 날짜를 다양한 형식으로 표시할 수 있으며, 주로 보고서처럼 출력 형식 요구사항이 있을 때 쓴다.
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y') AS formatted_date;
# 출력: Friday, September 01, 2023

WITH

  • WITH 구문은 MySQL에서 공통 테이블 표현식(CTE, Common Table Expression)을 정의할 때 사용된다.
  • CTE는 복잡한 쿼리를 더 읽기 쉽고 관리하기 쉽게 만들어 준다.
  • CTE는 MySQL 8.0 이상에서만 지원하므로, 5.7 이하에서는 파생 테이블이나 임시 테이블로 대체해야 한다.4

1. 기본 개념

  • CTE 정의: WITH 뒤에 CTE 이름과 그 정의를 작성한다. 일종의 임시 테이블로, 이후 메인 쿼리에서 참조할 수 있다.
  • 구조: 
    WITH cte_name AS (
    SELECT ... -- CTE 정의 쿼리
)
SELECT ...
FROM cte_name;

2. 사용 사례

  1. 복잡한 쿼리의 간소화: CTE를 사용하면 서브쿼리의 중첩을 피하고, 복잡한 쿼리를 단계별로 분리하여 이해하기 쉽게 만들 수 있다.

  2. 재사용 가능한 쿼리: 동일한 중간 결과를 여러 번 사용해야 할 때, CTE를 정의하고 이를 참조하면 중복된 코드를 줄일 수 있다.

# 예시
WITH AvgSalary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT employee_name, salary
FROM employees e
JOIN AvgSalary a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;
-- CTE `AvgSalary`: 각 부서의 평균 급여를 계산
-- 메인 쿼리: 이 CTE를 참조해 평균 급여보다 높은 직원들을 선택
  • 응용: 집계 함수와 조건문에서 해당하지 않는 값을 반환하는 방법
    • 집계 함수나 조건문을 쓸 때 특정 범주나 값이 데이터에 아예 존재하지 않는 경우가 있다. 이럴 때 아래처럼 기준이 되는 범주를 CTE로 먼저 만들어 두면, 해당 범주도 0건으로 함께 반환할 수 있다.
# 설명: 세 개의 상수 문자열을 각각 SELECT로 반환한 뒤, UNION 연산자로 하나의 집합으로 결합하는 쿼리다
# 첫 번째 SELECT는 'Low Salary'를 반환하고, 이 값을 category라는 별칭으로 지정한다.
WITH SalaryCategories AS (
    SELECT 'Low Salary' AS category
    UNION
    SELECT 'Average Salary'
    UNION
    SELECT 'High Salary'
)

  1. MySQL 8.4 Reference Manual — The DATE, DATETIME, and TIMESTAMP Types ↩

  2. MySQL 8.4 Reference Manual — Date and Time Functions ↩

  3. MySQL 8.4 Reference Manual — Date and Time Data Types ↩

  4. MySQL 8.4 Reference Manual — WITH (Common Table Expressions) ↩

댓글남기기

참고

자가 개선 에이전트 루프 설계: 5단계 아키텍처와 리워드 해킹 방어

llm

14 분 소요

자가 개선(self-improving) 루프는 사람 라벨 없이 프로덕션 트레이스를 학습 신호로 되돌리는 구조다. 롤아웃·스코어·필터·트레인·디플로이의 5단계 아키텍처, 복합 리워드 설계, PII·동의 게이트, 리워드 해킹 탐지와 방어, 조직 차원의 도입 판단 기준을 운영자 시점에서...