MySQL의 날짜 관련 데이터 타입
| 데이터 타입 | 설명 | 예시 |
|---|---|---|
| DATE | 연-월-일 형식의 날짜 정보를 가지는 데이터타입 | 2024-01-01 |
| TIME | HH:MM:SS 형식의 시간 정보를 가지는 데이터타입 | 12:00:35 |
| DATETIME | 날짜와 시간 정보를 모두 가지는 데이터타입. 1000-01-01 00:00:00부터 9999-12-31 23:59:59까지 |
1875-02-05 12:00:00 |
| TIMESTAMP | 날짜와 시간 정보를 모두 가지는 데이터타입 UTC 시간대에 대한 정보를 가지며 시간대 변환이 가능하다. ‘1970-01-01 00:00:01’부터 ‘2038-01-19 03:14:07’까지 |
2024-01-01 12:00:35 |
| YEAR | 연도 정보를 가지는 데이터타입 | 2024 |
현재 날짜 및 시간 출력
| 함수 | 설명 | input type | output type |
|---|---|---|---|
| NOW() | 쿼리를 실행하는 순간의 날짜와 시간을 반환 | None | DATETIME |
| SYSDATE() | 함수(자신)가 호출되는 순간의 날짜와 시간을 반환 | None | DATETIME |
| CURRENT_TIMESTAMP() | 쿼리를 실행하는 순간의 날짜와 시간을 반환 | None | TIMESTAMP |
| CURDATE() | 쿼리를 실행하는 순간의 날짜를 반환 | None | DATE |
| CURTIME() | 쿼리를 실행하는 순간의 시간을 반환 | None | TIME |
NOW()
쿼리를 실행하는 순간의 날짜와 시간을 반환합니다. 반환 타입은 DATETIME 입니다.
1
2
SELECT NOW();
-- >>> 2024-02-05 13:00:13.000
SYSDATE()
함수(자신)가 호출되는 순간의 날짜와 시간을 반환합니다. 반환 타입은 DATETIME 입니다.
1
2
SELECT SYSDATE()
-- >>> 2024-02-05 13:02:43.000
CURRENT_TIMESTAMP()
쿼리를 실행하는 순간의 날짜와 시간을 반환합니다.
NOW()와 비슷하지만 반환 타입이 TIMESTAMP 형식으로 다릅니다.
1
2
SELECT CURRENT_TIMESTAMP()
-- >>> 2024-02-19 12:28:34
CURDATE()
쿼리를 실행하는 순간의 날짜를 반환합니다. DATE 형식을 반환합니다.
1
2
SELECT CURDATE()
-- >>> 2024-02-05
CURTIME()
쿼리를 실행하는 순간의 시간을 출력합니다. TIME 형식을 반환합니다.
1
2
SELECT CURTIME()
-- >>> 13:02:14
NOW와 SYSDATE 의 차이
NOW는 쿼리를 실행한 순간, 그리고 SYSDATE 쿼리가 실행되면서 SYSDATE 함수가 호출된 순간을 반환합니다. 쉽게 말하면 NOW는 쿼리 자체가 실행된 순간, SYSDATE는 자기 자신이 실행된 순간을 반환하는 것입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT NOW(), SLEEP(5), NOW();
-- >> +---------------------+----------+---------------------+
-- >> | SYSDATE() | SLEEP(2) | SYSDATE() |
-- >> +---------------------+----------+---------------------+
-- >> | 2024-02-05 13:11:45 | 0 | 2024-02-05 13:11:45 |
-- >> +---------------------+----------+---------------------+
SELECT SYSDATE(), SLEEP(5), SYSDATE();
-- >> +---------------------+----------+---------------------+
-- >> | SYSDATE() | SLEEP(5) | SYSDATE() |
-- >> +---------------------+----------+---------------------+
-- >> | 2024-02-05 13:21:10 | 0 | 2024-02-05 13:21:15 |
-- >> +---------------------+----------+---------------------+
날짜와 관련된 다른 함수들을 보면 대부분 “쿼리를 실행한” 순간의 날짜나 시간을 반환합니다.
1
2
3
4
5
6
SELECT CURTIME(), SLEEP(5), CURTIME();
-- >> +-----------+----------+-----------+
-- >> | CURTIME() | SLEEP(5) | CURTIME() |
-- >> +-----------+----------+-----------+
-- >> | 13:49:45 | 0 | 13:49:45 |
-- >> +-----------+----------+-----------+
연, 월, 일, 요일
| 함수 | 설명 | output type |
|---|---|---|
| YEAR(날짜) | 날짜의 연도를 반환 | INT |
| MONTH(날짜) | 날짜의 월을 반환 | INT |
| DAY(날짜) | 날짜의 일을 반환 | INT |
| DAYOFMONTH(날짜) | 날짜가 해당 월의 몇 번째 일인지 반환 | INT |
| DAYOFYEAR(날짜) | 날짜가 해당 연도의 몇 번재 일인지 반환 | INT |
| WEEKDAY(날짜) | 날짜가 해당 주의 몇 번째 일인지 반환 0: 월요일 / 1: 화요일 .. / 6: 일요일 |
INT |
| DAYOFWEEK(날짜) | 날짜가 해당 주의 몇 번째 일인지 반환 1: 일요일 / 2: 월요일 … / 7: 토요일 |
INT |
| MONTHNAME(날짜) | 날짜의 월을 영문명으로 반환 | CHAR(9) |
| WEEK(날짜) | 날짜가 속한 주가 해당 연도의 몇 번째 주인지 반환 | INT |
YEAR(), MONTH(), DAY()
날짜의 연, 월, 일을 반환합니다.
1
2
3
4
5
6
7
8
SELECT NOW();
-- >> '2024-02-07'
SELECT YEAR(NOW());
-- >> 2024
SELECT MONTH(NOW());
-- >> 2
SELECT DAY('2024-02-07');
-- >> 7
DAYOF()
날짜가 해당 연도 혹은 월, 주의 몇 번째 일인지를 반환합니다.
1
2
3
4
5
6
7
8
9
10
SELECT NOW();
-- >> '2024-02-07'
SELECT DAYOFMONTH(NOW());
-- >> 7
SELECT DAYOFYEAR(NOW());
-- >> 38
SELECT DAYOFWEEK(NOW()); -- 일요일부터 1로 카운팅
-- >> 4
SELECT WEEKDAY(NOW()); -- 월요일부터 0으로 카운팅
-- >> 2
WEEK()
날짜가 속한 주가 해당 연도의 몇 번째 주인지 반환합니다.
1
2
3
4
SELECT NOW();
-- >> '2024-02-07'
SELECT WEEK(NOW());
-- >> 5
MONTHNAME()
날짜의 월을 영문명으로 반환합니다.
1
2
3
4
SELECT NOW();
-- >> '2024-02-07'
SELECT MONTHNAME(NOW());
-- >> February
DAYOFWEEK와 WEEKDAY 차이
둘 모두 대상 날짜가 해당 주의 몇 번째 날인지를 반환하는 함수입니다. 다른 점은 DAYOFWEEK 는 일요일을 1로 시작하여 카운팅을 하고, WEEKDAY는 월요일을 0으로 시작하여 카운팅을 한다는 점입니다.
1
2
3
4
5
6
7
8
9
-- 2024-02-07 기준 : 수요일
SELECT DAYOFWEEK(NOW());
-- >> 4
-- 일(1), 월(2), 화(3), 수(4)
SELECT WEEKDAY(NOW());
-- >> 2
-- 월(0), 화(1), 수(2)
DATE FORMAT
DATE_FORMAT(날짜, ‘형식’)
날짜를 형식에 맞게 반환합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT NOW();
-- >> 2024-02-07 12:58:50
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
-- >> 2024-02-07
SELECT DATE_FORMAT(NOW(), '%Y-%m');
-- >> 2024-02
SELECT DATE_FORMAT(NOW(), '%d');
-- >> 07
SELECT DATE_FORMAT(NOW(), '%D');
-- >> 7th
SELECT DATE_FORMAT(NOW(), '');
SELECT DATE_FORMAT(NOW(), '');
SELECT DATE_FORMAT(NOW(), '');
| 형식 | 설명 | 비고 | 예시 |
|---|---|---|---|
| %Y | 연도 | 네자리(nnnn)로 표현 | 2024 |
| %y | 연도 | 두자리(nn)로 표현 | 24 |
| %m | 월 | 두자리(nn)로 표현 | 02 |
| %c | 월 | 한자리(n) 혹은 두자리(nn)로 표현 | 2 |
| %M | 월 | 영문으로 표현 | February |
| %b | 월 | 영문 약어로 표현 | Feb |
| %d | 일 | 두자리(nn)로 표현 | 07 |
| %e | 일 | 한자리(n) 혹은 두자리(nn)로 표현 | 7 |
| %D | 일 | 영문 순서(1st, 2nd …)로 표현 | 7th |
| %a | 요일 | 영문 요일로 표현 (약어) | Wed |
| %W | 요일 | 영문 요일로 표현 (full) | Wednesday |
| %w | 요일 | 숫자 요일료 표현 (월요일부터 1) | 3 |
| %T | 시:분:초 | 24시간 셈법으로 hh:mm:ss | 18:44:55 |
| %r | 시:분:초 AM/PM | 12시간 셈법으로 hh:mm:ss AM/PM | 06:44:55 PM |
| %p | AM/PM | AM/PM | PM |
| %H | 시간 | 24시간 셈법으로 (ex. 23) 두자리(nn) | 18 |
| %h | 시간 | 12시간 셈법으로 (ex. 11) 두자리(nn) | 06 |
| %k | 시간 | 24시간 셈법으로 한자리(n) ~ 두자리(nn) | 18 |
| %l | 시간 | 12시간 셈법으로 한자리(n) ~ 두자리(nn) | 6 |
| %i | 분 | 두자리(nn)로 표현 | 05 |
| %S | 초 | 두자리(nn)로 표현 | 02 |
| %s | 초 | 두자리(nn)로 표현 (뭐가 다른거지?) | 02 |
| %f | 마이크로초 | 6자리 | 013451 |
| %j | 연도로부터의 일수 | 세자리(nnn)로 표현 | 038 |
| %U | 연도로부터의 주수 | 두자리(nn)로 표현. 주 시작은 월요일, 1부터 | 05 |
| %u | 연도로부터의 주수 | 두자리(nn)로 표현. 주 시작은 일요일, 0부터 | 06 |
| %V | 연도로부터의 주수 | 두자리(nn)로 표현. ISO8601표준에 따름. 월요일부터 | 05 |
| %v | 연도로부터의 주수 | 두자리(nn)로 표현. %V와 같은데 일요일부터 | 06 |
날짜 연산
| 함수 | 설명 | output_type |
|---|---|---|
| DATEDIFF(‘일자1’, ‘일자2’) | 두 날짜 사이의 일수 차이를 계산 | INT |
| DATE_ADD(‘날짜1’, INTERVAL 숫자 단위) DATE_ADD(‘timestamp1’, INTERVAL 숫자 단위) |
날짜나 시간을 더함 | 단위에 따라서 다름 |
| DATE_SUB(‘날짜1’, INTERVAL 숫자 단위) DATE_SUB(‘timestamp1’, INTERVAL 숫자 단위) |
날짜나 시간을 뺌 DATE_ADD와 사용법 동일. |
단위에 따라서 다름 |
| FROM_DAYS() | 일수를 DATETIME 형식으로 변환 | DATETIME |
| TO_DAYS() | DATETIME 형식을 일수로 변환 | INT |
| TIMESTAMPDIFF(단위, ‘timestamp1’, ‘timestamp2’) | 두 시간의 차이를 계산. 단위 : Second, Minute, Day, Month, Year |
단위에 따라서 다름 |
DATADIFF()
DATEDIFF 는 두 날짜 사이의 일수 차이를 계산합니다. 함수의 파라미터로 들어가는 순서대로 DATEDIFF(a, b) 면 a - b 의 일수를 구합니다.
1
2
3
4
5
SELECT DATEDIFF('2024-02-10', '2024-02-11');
-- >> -1
SELECT DATEDIFF('2024-02-20', '2024-02-10');
-- >> 10
일수 기준으로 계산을 하므로, DATETIME으로 2초가 차이나는 다음과 같은 경우에도 1일 차이로 값을 반환합니다.
1
2
SELECT DATEDIFF('2024-02-11 00:00:01', '2024-02-10 23:59:59');
-- >> 1
DATE_ADD(), DATE_SUB()
DATE_ADD : 지정 DATE 혹은 TIMESTAMP 에 대해 며칠, 몇개월, 몇년, 몇시간, 몇분, 몇초 후의 DATE 혹은 TIMESTAMP를 반환합니다.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT DATE_ADD('2024-01-01', INTERVAL 3 DAY);
-- >> 2024-01-04
SELECT DATE_ADD('2024-01-01', INTERVAL 3 MONTH);
-- >> 2024-04-01
SELECT DATE_ADD('2024-01-01', INTERVAL 3 YEAR);
-- >> 2027-01-01
SELECT DATE_ADD('2024-01-01', INTERVAL 3 HOUR);
-- >> 2024-01-01 03:00:00
SELECT DATE_ADD('2024-01-01', INTERVAL 3 MINUTE);
-- >> 2024-01-01 00:03:00
SELECT DATE_ADD('2024-01-01', INTERVAL 3 SECOND);
-- >> 2024-01-01 00:00:03
1
2
3
4
SELECT DATE_ADD('2024-01-01 12:00:00', INTERVAL 3 SECOND);
-- >> 2024-01-01 12:00:03
SELECT DATE_ADD('2024-01-01 12:00:00', INTERVAL 3 DAY);
-- >> 2024-01-04 12:00:00
DATE_SUB : 지정 DATE 혹은 TIMESTAMP 에 대해 며칠, 몇개월, 몇년, 몇시간, 몇분, 몇초 전의 DATE 혹은 TIMESTAMP를 반환합니다.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT DATE_SUB('2024-01-01', INTERVAL 3 DAY);
-- >> 2023-12-29
SELECT DATE_SUB('2024-01-01', INTERVAL 3 MONTH);
-- >> 2023-10-01
SELECT DATE_SUB('2024-01-01', INTERVAL 3 YEAR);
-- >> 2021-01-01
SELECT DATE_SUB('2024-01-01', INTERVAL 3 HOUR);
-- >> 2023-12-31 21:00:00
SELECT DATE_SUB('2024-01-01', INTERVAL 3 MINUTE);
-- >> 2023-12-31 23:57:00
SELECT DATE_SUB('2024-01-01', INTERVAL 3 SECOND);
-- >> 2023-12-31 23:59:57
1
2
3
4
SELECT DATE_SUB('2024-01-01 12:00:00', INTERVAL 3 SECOND);
-- >> 2024-01-01 11:59:57
SELECT DATE_SUB('2024-01-01 12:00:00', INTERVAL 3 DAY);
-- >> 2023-12-29 12:00:00
FROM_DAYS(), TO_DAYS()
FROM_DAYS 는 일수를 DATETIME 형식으로 바꿔줍니다. 그레고리안 캘린더를 따르며
365일까지 0년 0월 0일로 표기하고, 365.5일 부터 1년 1월 1일로 표시합니다.
1
2
3
4
5
6
7
8
9
10
SELECT FROM_DAYS(2024*365);
-- >> 2022-08-28
SELECT FROM_DAYS(2024*365.2425); -- 그레고리안력
-- >> 2024-01-01
SELECT FROM_DAYS(1);
-- >> 0000-00-00
SELECT FROM_DAYS(365);
-- >> 0000-00-00
SELECT FROM_DAYS(365.5);
-- >> 0001-01-01
TO_DAYS 는 0년부터 주어진 DATETIME 까지의 기간을 일수로 반환합니다. FROM_DAYS와 동일하게 그레고리안 캘린더를 따릅니다.
1
2
3
4
5
6
SELECT TO_DAYS('2024-01-01');
-- >> 739251
SELECT TO_DAYS('0001-01-01');
-- >> 366
SELECT TO_DAYS('0001-02-01');
-- >> 32
TIMESTAMPDIFF
두 TIMESTAMP 간의 차이를 지정한 단위를 적용하여 반환합니다.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT TIMESTAMPDIFF(YEAR, '2019-01-01 10:59:20', '2024-01-01 11:00:00');
-- >> 5
SELECT TIMESTAMPDIFF(MONTH, '2019-01-01 10:59:20', '2024-01-01 11:00:00');
-- >> 60
SELECT TIMESTAMPDIFF(DAY, '2019-01-01 10:59:20', '2024-01-01 11:00:00');
-- >> 1826
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 10:59:00', '2024-01-01 12:00:00');
-- >> 1
SELECT TIMESTAMPDIFF(MINUTE, '2024-01-01 11:59:00', '2024-01-01 12:00:00');
-- >> 1
SELECT TIMESTAMPDIFF(SECOND, '2024-01-01 11:59:00', '2024-01-01 12:00:00');
-- >> 60
1
2
3
4
5
SELECT TIMESTAMPDIFF(MINUTE, '2024-01-01 11:59:00', '2024-01-01 12:00:00');
-- >> 1
SELECT TIMESTAMPDIFF(MINUTE, '2024-01-01 11:59:20', '2024-01-01 12:00:00');
-- >> 0
-- 40초 차이가 있지만 분으로는 0분
Reference
mysql 날짜 연산 함수 : https://inpa.tistory.com
mysql 날짜 연산 함수 : https://dadev.tistory.com
mysql 날짜 연산 함수 : https://jaehoney.tistory.com/53
sysdate와 now의 차이 : https://thenicesj.tistory.com/68
sysdate와 now의 차이 : https://velog.io/@kimju0913
date format : https://lightblog.tistory.com/155
FROM_DAYS : https://www.w3schools.com
TO_DAYS : https://www.w3schools.com
TIMESTAMP와 DATETIME의 차이 : https://jaejade.tistory.com/125
날짜, 시간 데이터타입 : https://unabated.tistory.com