1. 시간/날짜 관련 함수들
- CURRENT_DATE, CURDATE: 현재 날짜 반환
- CURRENT_TIME, CURTIME: 현재 시간 반환
- CURRENT_TIMESTAMP, NOW: 현재 시간과 날짜 반환
SELECT CURDATE(), CURTIME(), NOW();
![](https://velog.velcdn.com/images/jeong_yooony/post/6e625960-a954-45e1-9b05-4a26857233a0/image.png)
- DATE: 문자열에 따라 날짜 생성
- TIME: 문자열에 따라 시간 생성
SELECT
'2021-6-1' = '2021-06-01',
DATE('2021-6-1') = DATE('2021-06-01'),
'1:2:3' = '01:02:03',
TIME('1:2:3') = TIME('01:02:03');
![](https://velog.velcdn.com/images/jeong_yooony/post/67e9726b-36c7-45b4-a7b0-e2027cd74445/image.png)
SELECT
'2021-6-1 1:2:3' = '2021-06-01 01:02:03',
DATE('2021-6-1 1:2:3') = DATE('2021-06-01 01:02:03'),
TIME('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'),
DATE('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'),
DATE('2021-6-1') = DATE('2021-06-01 01:02:03'),
TIME('2021-6-1 1:2:3') = TIME('01:02:03');
![](https://velog.velcdn.com/images/jeong_yooony/post/0533af21-ef90-496e-b34f-941c8cf46fe7/image.png)
SELECT * FROM Orders
WHERE
OrderDate BETWEEN DATE('1997-1-1') AND DATE('1997-1-31');
![](https://velog.velcdn.com/images/jeong_yooony/post/ac3f6b5d-9616-46b2-8032-4dba83ac76d3/image.png)
- YEAR: 주어진 DATETIME값의 년도 반환
- MONTHNAME: 주어진 DATETIME값의 월(영문) 반환
- MONTH 주어진: DATETIME값의 월 반환
- WEEKDAY: 주어진 DATETIME값의 요일값 반환(월요일: 0)
- DAYNAME: 주어진 DATETIME값의 요일명 반환
- DAYOFMONTH, DAY: 주어진 DATETIME값의 날짜(일) 반환
SELECT
OrderDate,
YEAR(OrderDate) AS YEAR,
MONTHNAME(OrderDate) AS MONTHNAME,
MONTH(OrderDate) AS MONTH,
WEEKDAY(OrderDate) AS WEEKDAY,
DAYNAME(OrderDate) AS DAYNAME,
DAY(OrderDate) AS DAY
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/dea8faf7-5a91-47df-a59d-0621eedee432/image.png)
SELECT
OrderDate,
CONCAT(
CONCAT_WS(
'/',
YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)
),
' ',
UPPER(LEFT(DAYNAME(OrderDate), 3))
)
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/df9b901f-4b8b-4ed1-af56-30495a14ef45/image.png)
SELECT * FROM Orders
WHERE WEEKDAY(OrderDate) = 0;
![](https://velog.velcdn.com/images/jeong_yooony/post/d48857f8-af59-49c1-ae5f-159b379af285/image.png)
- HOUR: 주어진 DATETIME의 시 반환
- MINUTE: 주어진 DATETIME의 분 반환
- SECOND: 주어진 DATETIME의 초 반환
SELECT
HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
![](https://velog.velcdn.com/images/jeong_yooony/post/f0005585-b901-4cea-be00-eae5aa2226ae/image.png)
- ADDDATE, DATE_ADD: 시간/날짜 더하기
- SUBDATE, DATE_SUB: 시간/날짜 빼기
SELECT
ADDDATE('2021-06-20', INTERVAL 1 YEAR),
ADDDATE('2021-06-20', INTERVAL -2 MONTH),
ADDDATE('2021-06-20', INTERVAL 3 WEEK),
ADDDATE('2021-06-20', INTERVAL -4 DAY),
ADDDATE('2021-06-20', INTERVAL -5 MINUTE),
ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);
![](https://velog.velcdn.com/images/jeong_yooony/post/4572fb88-c9dd-4466-a9f5-980aae5ab3e9/image.png)
SELECT
OrderDate,
ADDDATE(OrderDate, INTERVAL 1 YEAR),
ADDDATE(OrderDate, INTERVAL -2 MONTH),
ADDDATE(OrderDate, INTERVAL 3 WEEK),
ADDDATE(OrderDate, INTERVAL -4 DAY),
ADDDATE(OrderDate, INTERVAL -5 MINUTE)
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/865d1e19-89bf-4e67-960a-6019f6ec1a52/image.png)
- DATE_DIFF: 두 시간/날짜 간 일수차
- TIME_DIFF: 두 시간/날짜 간 시간차
SELECT
OrderDate,
NOW(),
DATEDIFF(OrderDate, NOW())
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/93baf933-fbc5-47ee-9d43-7d25bd073cb4/image.png)
SELECT
TIMEDIFF('2021-06-21 15:20:35', '2021-06-21 16:34:41');
![](https://velog.velcdn.com/images/jeong_yooony/post/a2174efc-baaa-4bb9-a3dd-089f91df3a98/image.png)
SELECT * FROM Orders
WHERE
ABS(DATEDIFF(OrderDate, '1996-10-10')) < 5;
![](https://velog.velcdn.com/images/jeong_yooony/post/f9ee818d-66f2-4943-b865-e224314fcf6e/image.png)
SELECT
OrderDate,
LAST_DAY(OrderDate),
DAY(LAST_DAY(OrderDate)),
DATEDIFF(LAST_DAY(OrderDate), OrderDate)
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/a14e0d62-1958-48ee-9d6c-710c4ac5c024/image.png)
- DATE_FORMAT: 시간/날짜를 지정한 형식으로 반환
- %Y: 년도 4자리
- %y: 년도 2자리
- %M: 월 영문
- %m: 월 숫자
- %D: 일 영문(1st, 2nd, 3rd...)
- %d, %e: 일 숫자 (01 ~ 31)
- %T: hh:mm:ss
- %r: hh:mm:ss AM/PM
- %H, %k: 시 (~23)
- %h, %l: 시 (~12)
- %i: 분
- %S, %s: 초
- %p: AM/PM
SELECT
DATE_FORMAT(NOW(), '%M %D, %Y %T'),
DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'),
DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');
![](https://velog.velcdn.com/images/jeong_yooony/post/42ffcca5-d7ea-484c-8d71-21f4d517ce28/image.png)
SELECT REPLACE(
REPLACE(
DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %초'),
'AM', '오전'
),
'PM', '오후'
)
![](https://velog.velcdn.com/images/jeong_yooony/post/9cdb8472-d3e6-4c21-b955-ed3660963269/image.png)
- STR TO DATE(S, F): S를 F형식으로 해석하여 시간/날짜 생성
SELECT
DATEDIFF(
STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
),
TIMEDIFF(
STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
);
![](https://velog.velcdn.com/images/jeong_yooony/post/42de59c9-7733-420a-81da-b2717618de58/image.png)
SELECT
OrderDate,
DATEDIFF(
STR_TO_DATE('1997-01-01 13:24:35', '%Y-%m-%d %T'),
OrderDate
),
TIMEDIFF(
STR_TO_DATE('1997-01-01 13:24:35', '%Y-%m-%d %T'),
STR_TO_DATE(CONCAT(OrderDate, ' ', '00:00:00'), '%Y-%m-%d %T')
)
FROM Orders;
![](https://velog.velcdn.com/images/jeong_yooony/post/2ee7f18c-cf7f-43e2-8b96-026a4d02d9ef/image.png)
🐬 더 많은 시간/날짜 함수 보러가기 ▶️
2. 기타 함수들
- IF(조건, T, F): 조건이 참이라면 T, 거짓이면 F 반환
SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.');
![](https://velog.velcdn.com/images/jeong_yooony/post/6615e935-12e4-4309-a5b5-5342f67ce9b9/image.png)
💡 보다 복잡한 조건은 CASE문을 사용합니다.
SELECT
CASE
WHEN -1 > 0 THEN '-1은 양수다.'
WHEN -1 = 0 THEN '-1은 0이다.'
ELSE '-1은 음수다.'
END;
![](https://velog.velcdn.com/images/jeong_yooony/post/5c5da7b7-359d-4acd-9c01-26f1263cead2/image.png)
SELECT
Price,
IF (Price > 30, 'Expensive', 'Cheap'),
CASE
WHEN Price < 20 THEN '저가'
WHEN Price BETWEEN 20 AND 30 THEN '일반'
ELSE '고가'
END
FROM Products;
![](https://velog.velcdn.com/images/jeong_yooony/post/67b53e63-a2d8-4da4-a1ee-c365cb817d44/image.png)
- IFNULL(A, B): A가 NULL일 시 B 출력
SELECT
IFNULL('A', 'B'),
IFNULL(NULL, 'B');
![](https://velog.velcdn.com/images/jeong_yooony/post/265966c8-a197-4cbb-a8a3-ed68d8dfa276/image.png)