[MySQL Function] 날짜, 시간 함수

2020. 4. 28. 11:58ㆍDB/MySQL

반응형

아래의 모든 함수에서 잘못된 입력값()을 넣을 시 NULL을 반환합니다.

특정기간을 날짜에 더하거나 빼는 함수

날짜를 더하는 함수는 ADDDATE(= DATE_ADD), 빼는 함수에는 SUBDATE(= DATE_SUB)가 있습니다.

ADDDATE와 SUBDATE는 더하고 빼는 것 외에 기능이 동일하므로 ADDDATE 함수만 설명하겠습니다.


ADDDATE

mysql> SET @T = '2020-03-13';

mysql> SELECT ADDDATE(@T, INTERVAL 1 DAY), ADDDATE(@T, INTERVAL 1 MONTH), ADDDATE(@T, INTERVAL 1 YEAR);
+-----------------------------+-------------------------------+------------------------------+
| ADDDATE(@T, INTERVAL 1 DAY) | ADDDATE(@T, INTERVAL 1 MONTH) | ADDDATE(@T, INTERVAL 1 YEAR) |
+-----------------------------+-------------------------------+------------------------------+
| 2020-03-14                  | 2020-04-13                    | 2021-03-13                   |
+-----------------------------+-------------------------------+------------------------------+

mysql> SELECT ADDDATE(@T, INTERVAL 1 SECOND), ADDDATE(@T, INTERVAL '1:1' MINUTE_SECOND);
+--------------------------------+-------------------------------------------+
| ADDDATE(@T, INTERVAL 1 SECOND) | ADDDATE(@T, INTERVAL '1:1' MINUTE_SECOND) |
+--------------------------------+-------------------------------------------+
| 2020-03-13 00:00:01            | 2020-03-13 00:01:01                       |
+--------------------------------+-------------------------------------------+

mysql> SELECT ADDDATE(@T, INTERVAL '1 1' DAY_HOUR), ADDDATE(@T, INTERVAL '2 6:30:0' DAY_SECOND);
+--------------------------------------+---------------------------------------------+
| ADDDATE(@T, INTERVAL '1 1' DAY_HOUR) | ADDDATE(@T, INTERVAL '2 6:30:0' DAY_SECOND) |
+--------------------------------------+---------------------------------------------+
| 2020-03-14 01:00:00                  | 2020-03-15 06:30:00                         |
+--------------------------------------+---------------------------------------------+


mysql> SELECT CURDATE(), ADDDATE(CURDATE(), 20);
+------------+------------------------+
| CURDATE()  | ADDDATE(CURDATE(), 20) |
+------------+------------------------+
| 2020-04-17 | 2020-05-07             |
+------------+------------------------+

INTERVAL을 생략하면 DAY로 연산합니다.

mysql> SELECT CURDATE(), ADDDATE(CURDATE(), -5);
+------------+------------------------+
| CURDATE()  | ADDDATE(CURDATE(), -5) |
+------------+------------------------+
| 2020-04-17 | 2020-04-12             |
+------------+------------------------+

음수값을 넣을 경우 날짜를 뺄 수 있습니다. (동의 함수: SUBDATE)

시간을 더하거나 빼는 함수

날짜를 더하는 함수는 SUBTIME, 빼는 함수에는 ADDTIME가 있습니다.
ADDTIME와 SUBTIME는 더하고 빼는 것 외에 기능이 동일하므로 SUBTIME 함수만 설명하겠습니다.

SUBTIME

시간을 빼는 함수는 DATE중 날짜(dd)까지는 함께 뺄 수 있습니다. (DAY_SECOND, DAY_HOUR, MINUTE_SECOND 형식)

mysql> SELECT NOW(), SUBTIME(NOW(), '00:30:00');
+---------------------+----------------------------+
| NOW()               | SUBTIME(NOW(), '00:30:00') |
+---------------------+----------------------------+
| 2020-03-06 10:55:18 | 2020-03-06 10:25:18        |
+---------------------+----------------------------+

mysql> SET @d = '2020-02-14 17:25:00';
mysql> SELECT @d, SUBTIME(@d, '25 09:30:00'), SUBTIME(@d, '2 1:1:1');
+---------------------+----------------------------+------------------------+
| @d                  | SUBTIME(@d, '25 09:30:00') | SUBTIME(@d, '2 1:1:1') |
+---------------------+----------------------------+------------------------+
| 2020-02-14 17:25:00 | 2020-01-20 07:55:00        | 2020-02-12 16:23:59    |
+---------------------+----------------------------+------------------------+

현재 날짜 또는 날짜+시각을 출력하는 함수

CURDATE (= CURRENT_DATE)

현재 날짜를 yyyy-MM-dd 형태로 출력합니다.
+0을 할경우 yyyyMMdd 형태로 출력됩니다.

mysql> SELECT CURDATE(), CURDATE()+0;
+------------+-------------+
| CURDATE()  | CURDATE()+0 |
+------------+-------------+
| 2020-04-17 |    20200417 |
+------------+-------------+

NOW (= CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP)

현재 날짜 시간 출력을 ('yyyy-MM-dd hh:mm:ss')합니다.
+0을 할 경우 yyyyMMddhhmmss 형태로 출력됩니다

mysql> SELECT NOW(), NOW()+0;
+---------------------+----------------+
| NOW()               | NOW()+0        |
+---------------------+----------------+
| 2020-04-17 15:46:29 | 20200417154629 |
+---------------------+----------------+

SYSDATE

함수(SYSDATE)가 실행되는 시점의 시각을 출력합니다.

mysql> SELECT SYSDATE(), now(), SLEEP(2), SYSDATE(), NOW();
+---------------------+---------------------+----------+---------------------+---------------------+
| SYSDATE()           | now()               | SLEEP(2) | SYSDATE()           | NOW()               |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2020-04-16 15:33:58 | 2020-04-16 15:33:58 |        0 | 2020-04-16 15:34:00 | 2020-04-16 15:33:58 |
+---------------------+---------------------+----------+---------------------+---------------------+

실행 시점의 시각을 출력하는 함수이기 때문에, index가 설정된 datetime또는 timestamp 타입의 컬럼을 조회할 경우, 인덱싱이 무시된 채 FULL TABLE SCAN됩니다.
(각 row당 실행시점을 새로 조회해야하기 때문에)

mysql> EXPLAIN SELECT * FROM USER WHERE create_dt >= NOW();
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | USER  | NULL       | range | create_dt     | create_dt | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

NOW() 함수는 sql 최초 실행시점의 시각을 기준으로 create_dt 인덱스를 조회하지만,

mysql> EXPLAIN SELECT * FROM USER WHERE create_dt >= SYSDATE();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | USER  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

SYSDATE() 함수는 각 row 조회할때마다 시각을 가져와야하기 때문에 인덱스를 이용하지 않고 FULL TABLE SCAN 됩니다

SYSDATE() 함수는 되도록 사용하지 말고 NOW() 함수를 사용하도록 합시다.

날짜 시간의 특정 부분만 추출하는 함수

DATE, YEAR, MONTH, DAY,
TIME, HOUR, MINUTE, SECOND, MICROSECOND

mysql> SET @T = '2020-03-13 09:20:11.123456';

mysql> SELECT DATE(@T), YEAR(@T), MONTH(@T), DAY(@T);
+------------+----------+-----------+---------+
| DATE(@T)   | YEAR(@T) | MONTH(@T) | DAY(@T) |
+------------+----------+-----------+---------+
| 2020-03-13 |     2020 |         3 |      13 |
+------------+----------+-----------+---------+

DAY는 DAYOFMONTH 함수와 동의함수입니다.

mysql> SELECT DAYOFWEEK(@T), DAYOFYEAR(@T);
+---------------+---------------+
| DAYOFWEEK(@T) | DAYOFYEAR(@T) |
+---------------+---------------+
|             6 |            73 |
+---------------+---------------+

주(week)기준 날짜 -> 요일. Sunday를 1로 표시합니다.

mysql> SELECT TIME(@T), HOUR(@T), MINUTE(@T), SECOND(@T), MICROSECOND(@T);
+-----------------+----------+------------+------------+-----------------+
| TIME(@T)        | HOUR(@T) | MINUTE(@T) | SECOND(@T) | MICROSECOND(@T) |
+-----------------+----------+------------+------------+-----------------+
| 09:20:11.123456 |        9 |         20 |         11 |          123456 |
+-----------------+----------+------------+------------+-----------------+


mysql> SELECT MONTHNAME(@T), DAYNAME(@T);
+---------------+-------------+
| MONTHNAME(@T) | DAYNAME(@T) |
+---------------+-------------+
| March         | Friday      |
+---------------+-------------+

날짜 차, 시간 차를 출력하는 함수

DATEDIFF

두 날짜를 뺀 값을 반환합니다.
datetime 형태를 넣을 경우 시간 정보는 무시합니다.

mysql> SELECT DATEDIFF('2020-04-17', '2020-04-02');
+--------------------------------------+
| DATEDIFF('2020-04-17', '2020-04-02') |
+--------------------------------------+
|                                   15 |
+--------------------------------------+

mysql> SELECT DATEDIFF('2020-04-17', '2020-04-02 09:00:00');
+-----------------------------------------------+
| DATEDIFF('2020-04-17', '2020-04-02 09:00:00') |
+-----------------------------------------------+
|                                            15 |
+-----------------------------------------------+

시간은 무시

mysql> SELECT DATEDIFF('2020-04-02', '2020-04-17');
+--------------------------------------+
| DATEDIFF('2020-04-02', '2020-04-17') |
+--------------------------------------+
|                                  -15 |
+--------------------------------------+

두번째에 들어온 date가 더 클 경우 음수로 출력


날짜 출력 형식을 설정하는 함수

DATE_FORMAT [참고]

mysql> SELECT DATE_FORMAT(@T, '%Y-%m-%d');
+-----------------------------+
| DATE_FORMAT(@T, '%Y-%m-%d') |
+-----------------------------+
| 2020-07-02                  |
+-----------------------------+

%y를 쓰면 연도의 끝자리 2자리만 출력합니다.

mysql> SELECT DATE_FORMAT(@T, '%T'), DATE_FORMAT(@T, '%r');
+-----------------------+-----------------------+
| DATE_FORMAT(@T, '%T') | DATE_FORMAT(@T, '%r') |
+-----------------------+-----------------------+
| 14:30:10              | 02:30:10 PM           |
+-----------------------+-----------------------+

%T = %H:%i:%s, %r = %h:%i:%s %p

mysql> SELECT DATE_FORMAT(@T, '%M %D %W'), DATE_FORMAT(@T, '%b %D %a');
+-----------------------------+-----------------------------+
| DATE_FORMAT(@T, '%M %D %W') | DATE_FORMAT(@T, '%b %D %a') |
+-----------------------------+-----------------------------+
| July 2nd Thursday           | Jul 2nd Thu                 |
+-----------------------------+-----------------------------+

날짜의 마지막 날짜를 출력하는 함수

LAST_DAY

입력받는 날짜의 맨마지막 DATE(actualMaximumDate)를 반환하는 함수입니다.

mysql> SELECT LAST_DAY('2020-02-03');
+------------------------+
| LAST_DAY('2020-02-03') |
+------------------------+
| 2020-02-29             |
+------------------------+

mysql> SELECT LAST_DAY('2020-10-09');
+------------------------+
| LAST_DAY('2020-10-09') |
+------------------------+
| 2020-10-31             |
+------------------------+

mysql> SELECT LAST_DAY('2020-01-32');
+------------------------+
| LAST_DAY('2020-01-32') |
+------------------------+
| NULL                   |
+------------------------+

잘못된 날짜를 입력받은 경우 NULL을 반환합니다


날짜나 시간형식 만드는 함수

MAKEDATE

MAKEDATE(year, dayofyear)

year와 dayOfYear를 이용하여 날짜형식 생성합니다.
첫번째 인자에 연도, 두번째 인자에 연 기준 날짜를 씁니다.

mysql> SELECT MAKEDATE(2021, 365), MAKEDATE(2021, 400);
+---------------------+---------------------+
| MAKEDATE(2021, 365) | MAKEDATE(2021, 400) |
+---------------------+---------------------+
| 2021-12-31          | 2022-02-04          |
+---------------------+---------------------+

연기준 최대날짜(365 또는 366)를 초과할 경우, 다음해로 출력됩니다.

mysql> SELECT MAKEDATE(2021, 0), MAKEDATE(20200, -1), MAKEDATE(0, 1);
+-------------------+---------------------+----------------+
| MAKEDATE(2021, 0) | MAKEDATE(20200, -1) | MAKEDATE(0, 1) |
+-------------------+---------------------+----------------+
| NULL              | NULL                | 2000-01-01     |
+-------------------+---------------------+----------------+

dayOfYear를 0이하의 숫자로 설정할 경우 NULL이 출력됩니다. 그 밖의 잘못된 값이 인자로 들어갈 경우에 마찬가지로 NULL이 출력됩니다.

MAKETIME

hour, minute, second를 입력받아 HH:mm:ss 형태로 시간데이터를 만듭니다.

mysql> SELECT MAKETIME(9, 10, 0), MAKETIME(20, 30, 10);
+--------------------+----------------------+
| MAKETIME(9, 10, 0) | MAKETIME(20, 30, 10) |
+--------------------+----------------------+
| 09:10:00           | 20:30:10             |
+--------------------+----------------------+

입력 날짜의 요일을 숫자로 반환하는 함수

WEEKDAY

입력 날짜의 요일을 숫자로 반환합니다.
월(0) ~ 일(6)

mysql> SELECT CURDATE(), WEEKDAY(CURDATE());
+------------+--------------------+
| CURDATE()  | WEEKDAY(CURDATE()) |
+------------+--------------------+
| 2020-04-06 |                  0 |
+------------+--------------------+

2020년 4월 6일을 월요일이라서 0

mysql> SELECT SUBDATE(CURDATE(), WEEKDAY(CURDATE())+7) last_monday,
    -> SUBDATE(CURDATE(), WEEKDAY(CURDATE())+1) last_sunday, CURDATE();
+-------------+-------------+------------+
| last_monday | last_sunday | CURDATE()  |
+-------------+-------------+------------+
| 2020-03-30  | 2020-04-05  | 2020-04-06 |
+-------------+-------------+------------+

mysql> SET @T = '2020-04-10';

mysql> SELECT WEEKDAY(@T), @T,
    -> SUBDATE(@T, WEEKDAY(@T)+7) last_monday, SUBDATE(@T, WEEKDAY(@T)+1) last_sunday;
+-------------+------------+-------------+-------------+
| WEEKDAY(@T) | @T         | last_monday | last_sunday |
+-------------+------------+-------------+-------------+
|           4 | 2020-04-10 | 2020-03-30  | 2020-04-05  |
+-------------+------------+-------------+-------------+

@T에 특정 날짜를 설정하고, 지난주 월요일과 일요일을 계산하는 방법입니다.

+++

  • MySQL Date and Time Functions
728x90
반응형
저작자표시 비영리 동일조건

'DB > MySQL' 카테고리의 다른 글

[MySQL Function] 암호화 알고리즘(AES, MD5, SHA1, SHA2)  (0) 2020.07.22
[MySQL Function] 숫자 함수  (0) 2020.07.21
[MySQL] 3. user(계정) 생성, 로그인, 변경, 삭제  (6) 2020.04.23
[MySQL] sql_mode로 알아보는 시스템 변수 permanent, runtime설정  (1) 2020.03.16
[MySQL] 2. database schema 조회, 생성, 변경, 삭제, 이름변경  (0) 2020.02.06

관련글

댓글 0

티스토리툴바