Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

DATE MATH

DATEDIFF() : 앞날짜 - 뒷날짜

mysql> SELECT DATEDIFF(NOW(), birthdate) FROM people;
+----------------------------+
| DATEDIFF(NOW(), birthdate) |
+----------------------------+
|                      13693 |
|                      28272 |
|                          1 |
+----------------------------+
3 rows in set (0.00 sec)

mysql> SELECT name, birthdate, DATEDIFF(NOW(),birthdate) FROM people;
+---------+------------+---------------------------+
| name    | birthdate  | DATEDIFF(NOW(),birthdate) |
+---------+------------+---------------------------+
| Padma   | 1983-11-11 |                     13693 |
| Larry   | 1943-12-12 |                     28272 |
| Toaster | 2021-05-07 |                         1 |
+---------+------------+---------------------------+
3 rows in set (0.00 sec)

DATE_ADD()

mysql> SELECT birthdt FROM people;
+---------------------+
| birthdt             |
+---------------------+
| 1983-11-11 10:07:35 |
| 1943-12-12 11:07:35 |
| 2021-05-07 23:15:55 |
+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people; 
+-------------------------------------+
| DATE_ADD(birthdt, INTERVAL 1 MONTH) |
+-------------------------------------+
| 1983-12-11 10:07:35                 |
| 1944-01-12 11:07:35                 |
| 2021-06-07 23:15:55                 |
+-------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people;
+---------------------------------------+
| DATE_ADD(birthdt, INTERVAL 10 SECOND) |
+---------------------------------------+
| 1983-11-11 10:07:45                   |
| 1943-12-12 11:07:45                   |
| 2021-05-07 23:16:05                   |
+---------------------------------------+
3 rows in set (0.00 sec)

만약 multiple things를 하고싶다. 예를들어 3달 12일을 더하고 싶다. 이런식으로 더하고 싶을때는 어떻게 해야할까?

mysql> SELECT DATE_ADD(birthdt,INTERVAL 3 MONTH  INTERVAL 12 DAY) FROM people;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERVAL 12 DAY) FROM people' at line 1


mysql> SELECT DATE_ADD(birthdt, INTERVAL 3 MONTH, INTERVAL 12 DAY) FROM people;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', INTERVAL 12 DAY) FROM people' at line 1

이런거는 안된다. 에러난다.

공식문서 이 문서를 조금 내려보면 답이 나온다. +/-연산자를 이용할 수 있다.

이걸 이용하면 위의 DATE_ADD() 함수를 안쓰고 그냥 바로 더하거나 빼버릴 수도 있다.

mysql> SELECT birthdt, birthdt + INTERVAL 3 MONTH + INTERVAL 12 DAY FROM people;
+---------------------+----------------------------------------------+
| birthdt             | birthdt + INTERVAL 3 MONTH + INTERVAL 12 DAY |
+---------------------+----------------------------------------------+
| 1983-11-11 10:07:35 | 1984-02-23 10:07:35                          |
| 1943-12-12 11:07:35 | 1944-03-24 11:07:35                          |
| 2021-05-07 23:15:55 | 2021-08-19 23:15:55                          |
+---------------------+----------------------------------------------+
3 rows in set (0.00 sec)