MySQL 날짜 및 시간 가이드: 데이터 타입, 함수, 포맷팅 및 범위 쿼리

1. 소개

MySQL은 웹 애플리케이션과 데이터베이스를 구축할 때 널리 사용되는 데이터베이스 관리 시스템이며, 날짜 데이터를 올바르게 처리하는 것이 특히 중요합니다. 예를 들어, 날짜와 관련된 데이터는 블로그 게시물 관리, 제품 판매 이력 추적, 사용자 로그인 이력 저장 등 다양한 상황에서 나타납니다. 날짜 데이터를 정확히 관리하면 데이터를 효율적으로 처리하고 사용자에게 정확한 정보를 제공할 수 있습니다.

이 가이드에서는 MySQL의 기본 날짜/시간 데이터 타입과 날짜 함수 사용법부터 날짜 기반 계산 및 범위 쿼리까지 포괄적으로 설명합니다. 이 내용은 초급부터 중급 사용자까지를 대상으로 하며, 실제 사용 사례를 기반으로 한 실용적인 쿼리 예제를 포함하여 일상 업무에 바로 활용할 수 있도록 구성했습니다.

이 글을 읽고 나면 다음과 같은 작업을 수행할 수 있습니다:

• MySQL 날짜/시간 데이터 타입의 특성을 이해하고 데이터에 적합한 타입을 선택할 수 있습니다.
• 날짜 함수를 사용하여 현재 날짜, 과거 날짜, 미래 날짜를 손쉽게 조회하고 조작할 수 있습니다.
• 날짜 범위 검색 및 비교를 수행하여 특정 기간의 데이터를 추출할 수 있습니다.

그럼 다음 섹션부터 MySQL 날짜의 기본 개념을 살펴보겠습니다.

2. MySQL 날짜/시간 데이터 타입 개요

MySQL에서 날짜를 관리할 때는 데이터와 사용 사례에 따라 적절한 날짜/시간 데이터 타입을 선택하는 것이 중요합니다. MySQL은 날짜와 시간을 처리하기 위해 여러 데이터 타입을 제공하며, 각각 특성과 사용 방법이 다릅니다. 이 섹션에서는 주요 날짜/시간 타입과 일반적인 사용 사례를 자세히 설명합니다.

DATE

DATE 타입은 날짜(년, 월, 일)만 저장하며 시간 구성 요소를 포함하지 않습니다. 지원 범위는 “1000-01-01”부터 “9999-12-31”까지이며, 1000년부터 9999년까지의 날짜를 처리할 수 있습니다. 생일이나 기념일처럼 시간과 무관한 날짜 전용 정보에 적합합니다.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

TIME 타입은 시간(시, 분, 초)을 저장합니다. 지원 범위는 “-838:59:59”부터 “838:59:59”까지입니다. 음수 시간을 표현할 수 있기 때문에 시간 차이를 나타내는 데에도 사용할 수 있습니다. 근무 시간이나 작업 소요 시간을 기록하는 데 유용합니다.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

DATETIME 타입은 날짜와 시간을 모두 저장합니다. 지원 범위는 “1000-01-01 00:00:00”부터 “9999-12-31 23:59:59”까지입니다. 시간대에 영향을 받지 않으며 저장된 값을 그대로 조회할 수 있습니다. 과거 사건의 타임스탬프나 향후 일정 기록에 적합합니다.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

TIMESTAMP 타입은 날짜와 시간을 저장하며 서버 시간대를 기준으로 값을 자동 변환합니다. 지원 범위는 “1970-01-01 00:00:01 UTC”부터 “2038-01-19 03:14:07 UTC”까지이며, Unix epoch 시간과 호환되어 타임스탬프 및 변경 이력 기록에 적합합니다. 또한 TIMESTAMP는 기본값으로 현재 시간을 설정할 수 있어 생성 및 업데이트 시간을 자동으로 기록하는 데 편리합니다.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

YEAR 타입은 연도만 저장합니다. 지원 범위는 “1901”부터 “2155”까지이며, 제조 연도나 설립 연도와 같이 특정 연도를 나타낼 때 유용합니다.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Comparison and Use Cases of Each Date/Time Type

Summary

MySQL 날짜/시간 데이터 유형은 데이터의 특성과 사용 계획에 따라 선택해야 합니다. 다음 섹션에서는 특히 혼동하기 쉬운 DATETIME과 TIMESTAMP의 차이점을 자세히 살펴보겠습니다.

3. DATETIME과 TIMESTAMP의 차이점

MySQL에서 날짜와 시간을 다룰 때 DATETIME과 TIMESTAMP가 일반적으로 사용되지만, 이들 사이에는 중요한 차이점이 있습니다. 두 타입 모두 연도, 월, 일, 시, 분, 초를 저장하지만, 시간대 처리 방식과 저장 가능한 시간 범위가 다릅니다. 사용 사례에 따라 선택해야 합니다. 이 섹션에서는 DATETIME과 TIMESTAMP의 차이점과 특성을 자세히 설명합니다.

DATETIME의 특성

• 시간대에 의존하지 않음 : DATETIME 유형은 서버 시간대 설정의 영향을 받지 않습니다. 저장된 값을 그대로 정확히 조회할 수 있습니다.
• 범위 : 1000-01-01 00:00:00부터 9999-12-31 23:59:59까지 지원합니다.
• 사용 사례 : 특정 시간대에 의존하지 않고 저장하고 싶은 데이터, 예를 들어 역사적 사건이나 향후 일정 등에 적합합니다.

예시: 이벤트 날짜/시간 저장

예를 들어, “보편적인” 날짜/시간 값을 그대로 유지하고 싶다면 DATETIME이 좋은 선택입니다. 아래 예시는 특정 날짜와 시간에 예정된 이벤트를 기록합니다.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

이 테이블에서 event_datetime에 저장된 날짜/시간은 시간대의 영향을 받지 않으며, 저장된 그대로 정확히 조회됩니다.

TIMESTAMP의 특성

• 시간대에 의존함 : TIMESTAMP 유형은 서버 시간대를 기준으로 저장 및 조회됩니다. 서로 다른 시간대의 서버 간에 데이터를 이동하면 그에 맞게 변환됩니다.
• 범위 : 1970-01-01 00:00:01 UTC부터 2038-01-19 03:14:07 UTC까지 지원합니다 (Unix 시간 범위 기반).
• 자동 업데이트 지원 : DEFAULT CURRENT_TIMESTAMP와 ON UPDATE CURRENT_TIMESTAMP를 사용하면 MySQL이 삽입 또는 업데이트 시 현재 타임스탬프를 자동으로 기록할 수 있습니다.
• 사용 사례 : 레코드가 업데이트될 때마다 타임스탬프가 변경되도록 자동으로 생성·수정 시간을 추적하고 싶을 때 가장 적합합니다.

예시: 게시물의 생성 및 수정 시간 저장

이 예시는 TIMESTAMP의 자동 업데이트 기능을 활용하여 블로그 게시물의 생성 및 수정 시간을 기록합니다.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

이 설정으로 created_at은 게시물이 처음 생성될 때 기록되고, updated_at은 게시물이 수정될 때마다 자동으로 업데이트됩니다.

비교 표: DATETIME vs TIMESTAMP

선택 방법

• 시간대 영향을 원하지 않을 경우 DATETIME 선택 : 예를 들어, 특정 국가·지역의 고정된 현지 시간으로 이벤트 시간을 저장하고 싶다면 DATETIME이 적합합니다.
• 자동 업데이트나 시간대 처리가 필요할 경우 TIMESTAMP 선택 : 예를 들어, 데이터베이스 행이 업데이트될 때마다 자동으로 기록하고 싶다면 TIMESTAMP가 편리합니다.

요약

DATETIME과 TIMESTAMP는 각각 다른 특성을 가지고 있습니다. 목적에 맞는 타입을 선택하면 날짜/시간 데이터를 보다 효율적으로 관리할 수 있습니다. 다음 섹션에서는 MySQL의 필수 날짜 함수들을 살펴보겠습니다.

4. MySQL 날짜/시간 함수 기본

MySQL은 현재 날짜/시간을 조회하거나 간격을 더하고 빼는 등 날짜와 시간을 다루기 위한 다양한 함수를 제공합니다. 이러한 함수들은 데이터베이스 관리 및 분석에 유용합니다. 이 섹션에서는 MySQL 날짜/시간 함수의 기본과 일반적인 사용 사례를 설명합니다.

현재 날짜/시간을 가져오는 함수

MySQL에서 현재 날짜와 시간을 가져오는 일반적인 함수 세 가지는 NOW(), CURDATE(), CURTIME()입니다.

NOW()

NOW() 함수는 현재 날짜와 시간을 YYYY-MM-DD HH:MM:SS 형식으로 반환합니다. 로그 기록 및 타임스탬프에 유용합니다.

SELECT NOW(); -- Get the current date and time

CURDATE()

CURDATE() 함수는 현재 날짜를 YYYY-MM-DD 형식으로 반환합니다. 시간은 포함되지 않으므로 날짜만 필요할 때 적합합니다.

SELECT CURDATE(); -- Get the current date

CURTIME()

CURTIME() 함수는 현재 시간을 HH:MM:SS 형식으로 반환합니다. 날짜 없이 시간만 필요할 때 사용합니다.

SELECT CURTIME(); -- Get the current time

Functions to Add/Subtract Dates

날짜에 간격을 더하거나 빼려면 DATE_ADD()와 DATE_SUB()를 사용합니다. 이 함수들을 사용하면 미래 또는 과거 날짜를 쉽게 계산할 수 있습니다.

DATE_ADD()

DATE_ADD() 함수는 지정된 간격을 날짜에 추가합니다. 예를 들어, 7일 후 또는 1개월 후 날짜를 구할 때 유용합니다.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

DATE_SUB() 함수는 지정된 간격을 날짜에서 뺍니다. 과거 날짜를 계산할 때 사용합니다.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Function to Calculate Date Differences

두 날짜 사이의 차이를 계산하려면 DATEDIFF()가 편리합니다. 예를 들어, 특정 날짜 이후 경과한 일수나 두 날짜 사이의 일수를 계산할 수 있습니다.

DATEDIFF()

DATEDIFF() 함수는 두 날짜 사이의 차이를 일(day) 단위로 반환합니다. 시작 날짜부터 종료 날짜까지의 일수를 확인하고 싶을 때 유용합니다.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Other Useful Date Functions

MySQL은 추가적인 유용한 날짜 함수를 제공합니다.

EXTRACT()

EXTRACT() 함수는 날짜에서 특정 부분(년, 월, 일 등)을 추출합니다. 날짜의 일부만 필요할 때 유용합니다.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

DATE_FORMAT() 함수는 날짜를 지정된 형식으로 표시합니다. 특정 형식(예: 일본식 포맷)으로 날짜를 표시하고 싶을 때 유용합니다.

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Date Formatting and Conversion Methods

MySQL에서는 날짜 표시 방식을 더 읽기 쉬운 형식으로 바꾸거나 날짜 문자열을 날짜 타입으로 변환할 수 있습니다. 이를 통해 표시 형식을 유연하게 제어하고 입력이 다양한 형식으로 들어와도 데이터를 일관되게 관리할 수 있습니다. 이 섹션에서는 날짜 포맷팅 및 변환에 사용되는 주요 함수를 설명합니다.

Date Formatting with the DATE_FORMAT() Function

DATE_FORMAT()을 사용하면 날짜 값을 지정된 형식으로 표시할 수 있습니다. 표준 형식이 아닌, 예를 들어 일본식 “YYYY년MM월DD일”과 같은 표시 형식이 필요할 때 특히 유용합니다.

Format Options

DATE_FORMAT()에서는 다음과 같은 포맷 옵션을 사용할 수 있습니다:

• %Y : 4자리 연도
• %y : 2자리 연도
• %m : 2자리 월 (01–12)
• %d : 2자리 일 (01–31)
• %H : 2자리 시 (00–23)
• %i : 2자리 분 (00–59)
• %s : 2자리 초 (00–59)

Example Usage

예를 들어, 날짜 2023-01-01을 “2023년01월01일”로 표시하려면 다음과 같이 작성할 수 있습니다:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

또한 슬래시를 사용해 날짜와 시간을 표시할 수 있습니다. 예: “2023/01/01 12:30:45.”

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

STR_TO_DATE()를 사용한 문자열을 날짜로 변환하기

STR_TO_DATE() 함수는 날짜 문자열을 MySQL 날짜 형식으로 변환합니다. 예를 들어, “2023年01月01日”와 같은 문자열을 DATE 값으로 다루고 싶을 때 유용합니다.

예시 사용법

문자열 “2023-01-01”을 DATE 값으로 변환하려면 다음과 같이 작성합니다:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

일본식 문자열 “2023年01月01日”을 날짜로 변환하려면 해당 형식을 지정합니다:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

다양한 형식의 날짜 변환 예시

실제 사용에서는 날짜 입력 형식이 다양할 수 있으므로, 서로 다른 형식에서 변환해야 할 수도 있습니다. 아래는 몇 가지 예시입니다.

1. “YYYY/MM/DD”를 DATE 값으로 변환

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. “MM-DD-YYYY”를 DATE 값으로 변환

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

이렇게 하면 데이터가 서로 다른 형식으로 입력되더라도 통합된 날짜 형식으로 일관되게 저장하고 관리할 수 있습니다.

DATE_FORMAT과 STR_TO_DATE의 차이점 및 각각 언제 사용해야 하는가

• DATE_FORMAT() : 기존 날짜 값의 표시 형식을 변경할 때 사용합니다. 변경은 표시만 영향을 주므로 저장된 데이터 자체는 수정되지 않습니다.
• STR_TO_DATE() : 날짜 문자열을 MySQL DATE 또는 DATETIME 값으로 변환할 때 사용합니다. 이는 저장된 데이터 유형을 변경하여 다른 MySQL 함수와 쿼리에서 날짜를 보다 쉽게 처리할 수 있게 합니다.

요약

DATE_FORMAT()와 STR_TO_DATE()를 사용하면 날짜 데이터의 표시와 저장 방식을 유연하게 관리할 수 있습니다. 이를 통해 사용자 또는 시스템 입력을 보다 유연하게 처리하면서도 일관된 날짜 관리를 유지할 수 있습니다. 다음 섹션에서는 날짜 계산 및 비교에 대해 설명하고, 날짜 데이터를 사용해 기간을 계산하고 비교하는 방법을 자세히 살펴보겠습니다.

6. 날짜 계산 및 비교

MySQL은 날짜 계산 및 비교를 위한 여러 편리한 함수를 제공합니다. 이를 통해 특정 기간의 데이터를 추출하거나 분석을 위해 날짜 차이를 계산할 수 있습니다. 이 섹션에서는 날짜 계산 및 비교에 사용되는 주요 함수와 사용 방법을 소개합니다.

날짜 차이 계산 함수: DATEDIFF()

DATEDIFF() 함수는 두 날짜 사이의 차이를 “일(day)” 단위로 반환합니다. 날짜 간 경과 일수를 확인하거나 과거 사건부터 오늘까지 며칠이 지났는지 확인할 때 유용합니다.

사용 예시

예를 들어, 2023년 1월 1일부터 2023년 1월 10일까지의 일수를 계산하려면 다음과 같이 작성합니다:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

이 예시에서 시작 날짜와 종료 날짜 사이의 차이는 9일이므로 결과는 9입니다.

TIMESTAMPDIFF()를 사용한 단위별 차이 계산

TIMESTAMPDIFF() 함수는 두 날짜 또는 datetime 사이의 차이를 지정된 단위(년, 월, 일, 시, 분, 초)로 계산합니다. 예를 들어 필요에 따라 “개월”이나 “시간” 단위의 차이를 구할 수 있습니다.

사용 예시

1. 개월 차이 계산

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. 시간 차이 계산

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

날짜 비교

날짜 비교는 MySQL의 표준 비교 연산자(<, >, <=, >=, =)를 사용하여 수행합니다. 이를 통해 특정 기간 내의 데이터를 추출하거나 과거/미래 날짜를 조건으로 포함할 수 있습니다.

사용 예시

예를 들어, 2023년 1월 1일 이후(포함)의 데이터를 추출하려면 다음과 같이 작성합니다:

SELECT * FROM events WHERE event_date >= '2023-01-01';

BETWEEN을 사용한 범위 지정

The BETWEEN 연산자를 사용하면 날짜 범위를 지정하여 데이터를 검색할 수 있습니다. 특정 기간에 해당하는 레코드를 추출하려는 경우에 유용합니다.

Example Usage

예를 들어, 2023년 1월 1일부터 2023년 1월 31일까지의 데이터를 검색하려면 다음과 같이 작성합니다:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

ADDDATE() 및 SUBDATE()를 사용한 날짜 계산

ADDDATE()와 SUBDATE()를 사용하면 날짜에 며칠을 더하거나 빼는 작업을 할 수 있습니다. 이는 미래 날짜나 과거 날짜를 계산할 때 편리합니다.

Example Usage

1. 날짜에 10일 추가

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. 날짜에서 10일 빼기

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

요약

MySQL 날짜 계산 및 비교 기능을 활용하면 특정 기간에 대한 데이터를 효율적으로 추출하고 시간 범위별로 데이터를 분석할 수 있습니다. 다음 섹션에서는 “날짜 범위 검색”이라는 보다 고급 주제를 자세히 살펴보겠습니다.