날짜 함수 파헤치기 - 문과생 SQL 독학 시리즈9

"Everything is available basically for free. You can learn anything you want for free." - Elon Musk의 인터뷰 중

머스크의 말처럼 모두 무료로 접할 수 있고 무엇이든 할 수 있으니..

무료로 접할 수 있는 정보들을 제게 맞게 최적화해서 정리해보려고 합니다.

 

이 글에선 날짜 함수에 대해 자세히 알아보겠습니다.

아래는 예시로 사용할 orders 테이블입니다.

order_id	order_date	customer_id
1	2023-01-01	1
2	2023-01-05	2
3	2023-02-10	3

 

1. CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP 함수

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

 

해석 > 현재 날짜, 현재 시간, 현재 날짜와 시간을 조회해줘

결과)

CURRENT_DATE	CURRENT_TIME	CURRENT_TIMESTAMP
2023-05-01	10:30:00	2023-05-01 10:30:00

 

2. YEAR, MONTH, DAY 함수

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

 

해석 >  orders 테이블에서 order_date와 order_date의 년도의 값을 year로, order_date의 월의 값을 month로 order_date의 일의 값을 day로 조회해줘

결과)

order_date	year	month	day
2023-01-01	2023	1	1
2023-01-05	2023	1	5
2023-02-10	2023	2	10

 

3. DATEDIFF 함수 - 두 날짜의 차이 계산

SELECT order_id, order_date, DATEDIFF(CURRENT_DATE, order_date) AS days_passed
FROM orders;

 

해석 > orders 테이블에서 order_id, order_date, order_date와 current_date의 차를 days_passed라는 이름으로 조회해줘

결과) !!current_date가 5월 1일일 때 기준!!

order_id	order_date	days_passed
1	2023-01-01	120
2	2023-01-05	116
3	2023-02-10	80

 

4. DATE_ADD, DATE_SUB 함수 - 특정 기간 더하기, 빼기

SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS one_week_later, DATE_SUB(order_date, INTERVAL 7 DAY) AS one_week_before
FROM orders;

 

해석 > orders 테이블에서 order_date, order_date에서 7일을 더한 값을 one_week_later로, order_date에서 7일을 뺀 값을 one_week_before로 조회해줘

결과)

order_date	one_week_later	one_week_before
2023-01-01	2023-01-08	2022-12-25
2023-01-05	2023-01-12	2022-12-29
2023-02-10	2023-02-17	2023-02-03

 

5. LAST_DAY - 해당 월의 마지막 날짜

select LAST_DAY(current_date) AS last_day_of_this_month;

 

해석 > current_date가 속한 월의 마지막 날짜를 last_day_of_this_month로 조회해줘

결과) !!current_date가 5월 1일일 때 기준!!

last_day_of_this_month

2023-05-01

 

6. TIMEDIFF - 두 날짜 사이의 차를 시간 단위로 계산

예시 테이블

orders1

order_id	customer_id	product_id	order_date	ship_date
1	1	1	2023-01-01 10:00:00	2023-01-03 12:00:00
2	2	3	2023-01-03 14:00:00	2023-01-07 17:00:00
3	1	2	2023-01-05 09:00:00	2023-01-10 11:00:00
4	3	4	2023-01-07 16:00:00	2023-01-11 19:00:00

 

SELECT order_id, order_date, ship_date,
       TIMEDIFF(ship_date, order_date) AS time_difference
FROM orders1;

 

해석 > orders1 테이블에서 order_id, order_date, ship_date, ship_date와 order_date 사이의 시간 차를 time_difference로 조회해줘

결과)

order_id	order_date	ship_date	time_difference
1	2023-01-01 10:00:00	2023-01-03 12:00:00	50:00:00
2	2023-01-03 14:00:00	2023-01-07 17:00:00	99:00:00
3	2023-01-05 09:00:00	2023-01-10 11:00:00	122:00:00
4	2023-01-07 16:00:00	2023-01-11 19:00:00	99:00:00

 

7. 시간을 일과 시간으로 구분하기

SELECT order_id, order_date, ship_date,
       TIMEDIFF(ship_date, order_date) AS time_difference,
       FLOOR(TIMESTAMPDIFF(HOUR, order_date, ship_date) / 24) AS days_difference,
       TIMESTAMPDIFF(HOUR, order_date, ship_date) % 24 AS hours_difference
FROM orders1;

 

해석 > orders1 테이블에서 order_id, order_date, ship_date, ship_date와 order_date 사이의 시간 차를 time_difference로, order_date와 ship_date의 시간 차를 24시간으로 나누고 소숫점은 버린 값을 days_difference로, order_date와 ship_date의 시간 차를 24시간으로 나누고 남은 값을 hours_difference로 조회해줘

결과)

order_id	order_date	ship_date	time_difference	days_difference	hours_difference
1	2023-01-01 10:00:00	2023-01-03 12:00:00	50:00:00	2	2
2	2023-01-03 14:00:00	2023-01-07 17:00:00	99:00:00	4	3
3	2023-01-05 09:00:00	2023-01-10 11:00:00	122:00:00	5	2
4	2023-01-07 16:00:00	2023-01-11 19:00:00	99:00:00	4	3

 

여기서 '/'는 나눗셈을 의미합니다. '%'는 나눈 나머지를 의미합니다.

 

8. 참고 문헌

통계 교육원 SQL 강의: 데이터 분석을 위한 SQL 입문(2023) - https://sti.kostat.go.kr/coresti/site/main.do

부스트코스 SQL 강의: 기초 데이터 분석을 위한 핵심 SQL - https://www.boostcourse.org/

시간 함수 mysql 공식 문서 - https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html