poeun 🔅

Original Artwork © Henri Matisse (1869 ~ 1954), Le Cheval L’Ecuyere et le Clown, 1947, 64.6 x 42.5, Centre Georges-Pompidou

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

3 분 소요


MySQL에서 날짜 및 시간을 다루는 기능은 매우 강력하며, 다양한 날짜 형식을 지원합니다. 또한 날짜를 계산하거나 비교할 때 사용하는 함수와 구문이 여러 가지 제공됩니다. 이 글에서는 MySQL에서 날짜를 처리하는 방법과 날짜 비교 또는 날짜 사이의 값 추출에 대해 설명합니다.


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

  • MySQL에서 날짜와 시간을 저장하는 주요 데이터 타입은 다음과 같습니다:
  • DATE: YYYY-MM-DD 형식으로 날짜만 저장합니다.
    • 예: '2023-09-01'
  • DATETIME: YYYY-MM-DD HH:MM:SS 형식으로 날짜와 시간을 모두 저장합니다.
    • 예: '2023-09-01 15:30:00'
  • TIMESTAMP: UTC(협정 세계시) 기준으로 날짜와 시간을 저장하며, 시스템의 타임존을 반영해 조정할 수 있습니다.
    • 예: '2023-09-01 15:30:00'
  • TIME: 시간만 저장합니다 (HH:MM:SS).
    • 예: '15:30:00'
  • YEAR: 연도만 저장합니다 (YYYY).
    • 예: '2023'


2. MySQL의 날짜 관련 함수

  • MySQL에서는 날짜와 시간을 처리하기 위한 다양한 함수가 제공됩니다. 자주 사용되는 함수는 다음과 같습니다:

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`은 경계 값을 포함**하므로, 해당 날짜도 포함됩니다.


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

  • 때로는 서브쿼리를 사용하여 특정 조건에 맞는 날짜 범위를 추출할 수도 있습니다.
# 이 쿼리는 **최근 7일 내의 주문**을 조회하는 쿼리입니다. `CURDATE()` 함수와 **`INTERVAL`**을 사용하여 동적으로 조건을 설정할 수 있습니다.
SELECT *
FROM Orders
WHERE order_date >= (SELECT CURDATE() - INTERVAL 7 DAY);


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는 복잡한 쿼리를 더 읽기 쉽고 관리하기 쉽게 만들어줍니다.

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를 참조해 평균 급여보다 높은 직원들을 선택


  • 응용: 집계 함수와 조건문에서 해당하지 않는 값을 반환하는 방법
    • 쿼리에서 집계 함수나 조건문을 사용할 때, 특정 범주나 값이 데이터에 존재하지 않는 경우가 발생할 수 있습니다. 이를 처리하기 위해, 다음과 같이 서브쿼리를 정의할 수 있습니다.
# 설명: 세 개의 상수 문자열을 각각 SELECT로 반환한 뒤, UNION 연산자를 사용하여 그 결과를 하나의 집합으로 결합하는 쿼리입니다
# 첫번재에서 'Low Salary'라는 문자열을 반환하고, 이 값을 category라는 별칭으로 지정합니다.
WITH SalaryCategories AS (
    SELECT 'Low Salary' AS category
    UNION
    SELECT 'Average Salary'
    UNION
    SELECT 'High Salary'    
)


댓글남기기

참고