날짜/시간 관련 함수 톺아보기
[SQL]**날짜/시간 관련 함수 톺아보기 — DATETIME, TIMESTAMP, EXTRACT, DATETIME_TRUNC, DATETIME_DIFF, PARSE_DATETIME
1. 시작하며
플젝할 때 쿼리문 짜면서 DW나 DM를 구축하던 중, 문득 날짜/시간 관련 함수들이 좀 헷갈렸었음.
예를 들어, 날짜 포맷이 안 맞아서 데이터가 깨지거나, 집계값이 이상하게 나와서 한참을 디버깅했던 기억이 있음.
흠,, 이에 방지하고자 이번 기회에 SQL에서 자주 쓰는 날짜/시간 관련 함수들을 톺아보는 시간을 가지려 함.
2. 날짜 및 시간 데이터의 핵심
2-1. 날짜 및 시간 데이터 타입 파악하기
| 타입 | 설명 | 예시 |
|---|---|---|
| DATE | 날짜만 있음 | 2025-04-03 |
| DATETIME | 날짜 + 시간, 타임존 정보 없음 | 2025-04-03 14:00:00 |
| TIME | 시간만 있음 (날짜 없음) | 23:59:59.00 |
| TIMESTAMP | UTC 기준 시점 기록, 타임존 포함 | 2025-04-03T05:00:00Z |
참고: 대부분의 시스템에서는 로그성 데이터가
TIMESTAMP로 저장되어 있음
2-2. 알아두면 좋은 개념: UTC, GMT, 밀리초
- UTC (협정 세계시): 전 세계 공통 기준 시간. 한국은
UTC+9 - GMT (그리니치 표준시): 영국 기준 시간. 사실상 UTC와 같게 취급
- Millisecond (ms): 1초 = 1,000ms. 초보다 정밀한 시간 단위가 필요할 때 사용.
- Microsecond (µs): 1ms = 1,000µs = 1,000,000µs = 1초. 매우 빠른 처리 시간 측정에 사용됨.
눈을 한 번 깜빡이는 데 걸리는 시간 ≒ 100ms 정도라고 함.
2-3. 밀리초 / 마이크로초 → 시간 변환
시스템 로그, 이벤트 트래킹 데이터는 밀리초 또는 마이크로초로 저장되어 있는 경우가 많다. 이를 사람이 읽을 수 있는 DATETIME으로 바꾸는 데 사용.
SELECT
TIMESTAMP_MILLIS(1743679200000) AS milli_to_timestamp_value,
TIMESTAMP_MICROS(1743679200000000) AS micro_to_timestamp_value,
DATETIME(TIMESTAMP_MICROS(1743679200000000)) AS datetime_value;
결과 예시 (2025-04-03 기준)
| 컬럼명 | 결과값 |
|---|---|
| milli_to_timestamp_value | 2025-05-03 00:00:00 UTC |
| micro_to_timestamp_value | 2025-05-03 00:00:00 UTC |
| datetime_value | 2025-05-03 09:00:00 |
DATETIME은 타임존이 없기 때문에 Asia/Seoul 기준으로 보면 9시간 더한 값이 나옴
3. 시간 데이터 타입 변환하기
3-1. TIMESTAMP → DATETIME
TIMESTAMP는 기본적으로 UTC 기준이라, 사람이 보기 좋게 지역 시간으로 바꿔야 할 때가 많다. DATETIME(TIMESTAMP, 'Asia/Seoul')처럼 타임존을 넣으면 현지 시각으로 변환 가능.
SELECT
CURRENT_TIMESTAMP() AS timestamp_col,
DATETIME(CURRENT_TIMESTAMP(), 'Asia/Seoul') AS datetime_col;
결과 예시 (2025-04-03 기준, 실행 시각 15:34)
| 컬럼명 | 결과값 |
|---|---|
| timestamp_col | 2025-04-03 06:34:00 UTC |
| datetime_col | 2025-04-03 15:34:00 |
3-2. CURRENT 계열 함수 총정리
시스템 시간이나 현재 날짜를 출력할 때 사용하는 함수들. Asia/Seoul 같이 지역을 명시하면 로컬 타임 기준으로 출력됨.
SELECT
CURRENT_DATE() AS current_date,
CURRENT_DATE("Asia/Seoul") AS asia_date,
CURRENT_DATETIME() AS current_datetime,
CURRENT_DATETIME("Asia/Seoul") AS current_datetime_asia;
결과 예시
| current_date | asia_date | current_datetime | current_datetime_asia |
|---|---|---|---|
| 2025-04-03 | 2025-04-03 | 2025-04-03 06:34:00 | 2025-04-03 15:34:00 |
4. 시간 함수 정복하기 (추출, 자르기, 변환 등)
4-1. EXTRACT 함수
날짜/시간 타입에서 원하는 부분만 추출할 때 사용. 연도, 월, 일, 시, 분, 요일 등 다양한 단위를 추출할 수 있음.
SELECT
EXTRACT(YEAR FROM DATETIME "2025-04-03 14:00:00") AS year,
EXTRACT(MONTH FROM DATETIME "2025-04-03 14:00:00") AS month,
EXTRACT(DAY FROM DATETIME "2025-04-03 14:00:00") AS day,
EXTRACT(HOUR FROM DATETIME "2025-04-03 14:00:00") AS hour,
EXTRACT(MINUTE FROM DATETIME "2025-04-03 14:00:00") AS minute,
EXTRACT(DAYOFWEEK FROM DATETIME "2025-04-03 14:00:00") AS dayofweek;
결과 예시
| year | month | day | hour | minute | dayofweek |
|---|---|---|---|---|---|
| 2025 | 4 | 3 | 14 | 0 | 5 |
DAYOFWEEK: 일요일=1, 금요일=5
4-2. DATETIME_TRUNC 함수
날짜/시간 값을 원하는 단위로 잘라주는 함수. 예를 들어 시간 단위로 잘라서 ‘2025-04-03 14:00:00’처럼 만들 수 있음. 주로 시간별, 일별 집계에 많이 씀.
SELECT
DATETIME_TRUNC(DATETIME "2025-04-03 14:42:13", DAY) AS day_trunc,
DATETIME_TRUNC(DATETIME "2025-04-03 14:42:13", HOUR) AS hour_trunc;
결과 예시
| day_trunc | hour_trunc |
|---|---|
| 2025-04-03 00:00:00 | 2025-04-03 14:00:00 |
4-3. PARSE_DATETIME / FORMAT_DATETIME
PARSE_DATETIME: 문자열을 DATETIME 타입으로 변환할 때 사용FORMAT_DATETIME: DATETIME을 원하는 문자열 형식으로 바꿀 때 사용
SELECT
PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2025-04-03 15:00:00') AS parsed,
FORMAT_DATETIME('%Y-%m-%d', DATETIME '2025-04-03 15:00:00') AS formatted;
결과 예시
| parsed | formatted |
|---|---|
| 2025-04-03 15:00:00 | 2025-04-03 |
4-4. LAST_DAY 함수
특정 날짜를 기준으로 그 달의 마지막 날을 구하거나, 주 단위 마지막 날을 구하고 싶을 때 사용.
SELECT
LAST_DAY(DATETIME '2025-04-03 15:00:00') AS last_day_month,
LAST_DAY(DATETIME '2025-04-03 15:00:00', WEEK(MONDAY)) AS last_day_week_mon;
결과 예시
| last_day_month | last_day_week_mon |
|---|---|
| 2025-04-30 00:00:00 | 2025-04-06 00:00:00 |
4-5. DATETIME_DIFF 함수
두 DATETIME 값 간의 차이를 구할 때 사용. DAY, MONTH, YEAR 등 단위를 정할 수 있음. 주로 집계 기준 기간 계산, 리텐션 분석 등에 유용함.
SELECT
DATETIME_DIFF(DATETIME '2025-04-03 15:00:00', DATETIME '2022-01-01 00:00:00', DAY) AS day_diff,
DATETIME_DIFF(DATETIME '2025-04-03 15:00:00', DATETIME '2022-01-01 00:00:00', MONTH) AS month_diff;
결과 예시
| day_diff | month_diff |
|---|---|
| 1189 | 39 |
TIMESTAMP 간에도 동일한 방식으로 사용할 수 있음
4-6. DATE_SUB / DATE_ADD
특정 날짜에서 며칠을 더하거나 뺄 때 사용함. 데이터 필터 조건에서 자주 등장.
SELECT
DATE_SUB(DATE '2025-04-03', INTERVAL 7 DAY) AS one_week_before,
DATE_ADD(DATE '2025-04-03', INTERVAL 3 MONTH) AS three_months_later;
결과 예시
| one_week_before | three_months_later |
|---|---|
| 2025-03-27 | 2025-07-03 |
5. 정리하자면 이렇다.
5-1. 시간 타입 비교
| 타입 | 구성 내용 | 타임존 여부 | 예시 |
|---|---|---|---|
| DATE | 연, 월, 일 | X | 2025-04-03 |
| DATETIME | 연, 월, 일, 시, 분, 초 | X | 2025-04-03 14:00:00 |
| TIME | 시, 분, 초 | X | 23:59:59 |
| TIMESTAMP | 연, 월, 일, 시, 분, 초 + 타임존 | O (UTC) | 2025-04-03T05:00:00Z |
정리하자면, 실무에서 데이터가 많이 쌓이는 로그성 데이터는
TIMESTAMP로 저장되는 경우가 많고, 사람이 보기 좋게 바꿀 때DATETIME으로 변환해서 쓰는 흐름임.
5-2. 변환 함수 요약
| 목적 | 함수명 | 설명 |
|---|---|---|
| 밀리초 → TIMESTAMP | TIMESTAMP_MILLIS() |
millisecond 정수값을 timestamp로 변환 |
| 마이크로초 → TIMESTAMP | TIMESTAMP_MICROS() |
microsecond 정수값을 timestamp로 변환 |
| TIMESTAMP → DATETIME | DATETIME() |
타임존 정보를 반영한 사람이 읽기 쉬운 시간으로 변환 |
| 문자열 → DATETIME | PARSE_DATETIME() |
텍스트를 DATETIME으로 파싱함 |
| DATETIME → 문자열 | FORMAT_DATETIME() |
DATETIME을 원하는 포맷 문자열로 출력 |
5-3. 시간 처리 함수 요약
| 기능 | 함수명 | 설명 |
|---|---|---|
| 현재 시간 | CURRENT_DATETIME() |
현재 시간을 반환 |
| 시간 일부 추출 | EXTRACT() |
연, 월, 일, 시, 분 등 특정 부분만 추출 |
| 시간 단위 자르기 | DATETIME_TRUNC() |
특정 단위로 시간을 자름 (ex. 시 단위, 일 단위) |
| 시간 간 차이 계산 | DATETIME_DIFF() |
두 시간 간의 차이를 구함 (DAY, MONTH, WEEK 등) |
| 월 or 주 마지막 날 구하기 | LAST_DAY() |
해당 월/주 마지막 날 구함 |
| 날짜 더하기/빼기 | DATE_ADD() / DATE_SUB() |
기간 기준으로 날짜를 앞뒤로 이동 |
참고:
DATE_DIFF,DATE_TRUNC는DATETIME_DIFF,DATETIME_TRUNC와 같은 구조로 사용 가능함.DATE타입에만 적용된다는 점만 다름.
5-4. EXTRACT vs DATETIME_TRUNC
| 상황 | 추천 함수 | 예시 사용 상황 |
|---|---|---|
| 특정 필드 값만 뽑고 싶을 때 | EXTRACT() |
연도, 월, 요일, 시 추출 등 |
| 시간을 특정 단위로 잘라서 집계 시 | DATETIME_TRUNC() |
1시간 단위 매출, 일 단위 방문자 수 집계 등 |
댓글남기기