Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
Formatting DATES
DAY(), DAYNAME(), DAYOFWEEK(), DAYOFYEAR
DAY() : DATE형식의 포맷('YYYY-MM-DD')또는 DATETIME형식의 포맷('YYYY-MM-DD HH:MM:SS')에서 날짜부분('DD')부분만 뽑아준다.
mysql> SELECT * FROM people;
+---------+------------+-----------+---------------------+
| name | birthdate | birthtime | birthdt |
+---------+------------+-----------+---------------------+
| Padma | 1983-11-11 | 10:07:35 | 1983-11-11 10:07:35 |
| Larry | 1943-12-12 | 11:07:35 | 1943-12-12 11:07:35 |
| Toaster | 2021-05-07 | 23:15:55 | 2021-05-07 23:15:55 |
+---------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT DAY(birthdate) FROM people;
+----------------+
| DAY(birthdate) |
+----------------+
| 11 |
| 12 |
| 7 |
+----------------+
3 rows in set (0.00 sec)
mysql> SELECT DAY(birthdt) FROM people;
+--------------+
| DAY(birthdt) |
+--------------+
| 11 |
| 12 |
| 7 |
+--------------+
3 rows in set (0.00 sec)
TIME형식('HH:MM:SS')을 넣어도 결과가 나오긴 하지만 답도 틀리고 이게 뭔 결과인지 모르겠다. TIME형식에는 쓰면 안된다.
mysql> SELECT DAY(birthtime) FROM people;
+----------------+
| DAY(birthtime) |
+----------------+
| 7 |
| 7 |
| 7 |
+----------------+
3 rows in set (0.00 sec)DAYNAME() : DATE형식의 포맷('YYYY-MM-DD')또는 DATETIME형식의 포맷('YYYY-MM-DD HH:MM:SS')을 입력하면 몇요일인지 알려준다.
mysql> SELECT * FROM people;
+---------+------------+-----------+---------------------+
| name | birthdate | birthtime | birthdt |
+---------+------------+-----------+---------------------+
| Padma | 1983-11-11 | 10:07:35 | 1983-11-11 10:07:35 |
| Larry | 1943-12-12 | 11:07:35 | 1943-12-12 11:07:35 |
| Toaster | 2021-05-07 | 23:15:55 | 2021-05-07 23:15:55 |
+---------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT DAYNAME(birthdate) FROM people;
+--------------------+
| DAYNAME(birthdate) |
+--------------------+
| Friday |
| Sunday |
| Friday |
+--------------------+
3 rows in set (0.00 sec)
mysql> SELECT DAYNAME(birthdt) FROM people;
+------------------+
| DAYNAME(birthdt) |
+------------------+
| Friday |
| Sunday |
| Friday |
+------------------+
3 rows in set (0.00 sec)
이것도 마찬가지로 TIME 형식의 자료형을 넣으면 이상한 결과값을 낸다. TIME형식에는 쓰지말자.
mysql> SELECT DAYNAME(birthtime) FROM people;
+--------------------+
| DAYNAME(birthtime) |
+--------------------+
| Friday |
| Friday |
| Friday |
+--------------------+
3 rows in set (0.00 sec)DAYOFWEEK : Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.
mysql> SELECT * FROM people;
+---------+------------+-----------+---------------------+
| name | birthdate | birthtime | birthdt |
+---------+------------+-----------+---------------------+
| Padma | 1983-11-11 | 10:07:35 | 1983-11-11 10:07:35 |
| Larry | 1943-12-12 | 11:07:35 | 1943-12-12 11:07:35 |
| Toaster | 2021-05-07 | 23:15:55 | 2021-05-07 23:15:55 |
+---------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT DAYOFWEEK(birthdate) FROM people;
+----------------------+
| DAYOFWEEK(birthdate) |
+----------------------+
| 6 |
| 1 |
| 6 |
+----------------------+
3 rows in set (0.00 sec)
mysql> SELECT DAYOFWEEK(birthdt) FROM people;
+--------------------+
| DAYOFWEEK(birthdt) |
+--------------------+
| 6 |
| 1 |
| 6 |
+--------------------+
3 rows in set (0.00 sec)
마찬가지로 TIME형에 넣으면 이상한 결과값이 나오니 쓰지말자.
mysql> SELECT DAYOFWEEK(birthtime) FROM people;
+----------------------+
| DAYOFWEEK(birthtime) |
+----------------------+
| 6 |
| 6 |
| 6 |
+----------------------+
3 rows in set (0.00 sec)DAYOFYEAR() : Returns the day of the year for date, in the range 1 to 366.
mysql> SELECT DAYOFYEAR(birthdate) FROM people;
+----------------------+
| DAYOFYEAR(birthdate) |
+----------------------+
| 315 |
| 346 |
| 127 |
+----------------------+
3 rows in set (0.00 sec)
mysql> SELECT DAYOFYEAR(birthdt) FROM people;
+--------------------+
| DAYOFYEAR(birthdt) |
+--------------------+
| 315 |
| 346 |
| 127 |
+--------------------+
3 rows in set (0.00 sec)역시나 TIME속성엔 안쓰는 걸로
mysql> SELECT DAYOFYEAR(birthtime) FROM people;
+----------------------+
| DAYOFYEAR(birthtime) |
+----------------------+
| 127 |
| 127 |
| 127 |
+----------------------+
3 rows in set (0.00 sec)++이 외에도 공식문서에 들어가보면 여러가지 많다.
- MONTH() : 달을 알려준다.(몇월달인지 : 1,2,3...),
- MONTHNAME() : 달의 이름을 알려준다 : ('January, Faburary, March...'),
TIME형과 함께 쓰일 수 있는 function들도 있다.
- HOUR : 시간을 알려준다. (당연히 TIME이나 DATETIME형식과 함께 써야한다. DATE형식과 쓰면 이상한 결과값)
- MINUTE : 분을 알려준다.(당연히 TIME이나 DATETIME형식과 함께 써야한다. DATE형식과 쓰면 이상한 결과값)
- Q. "2017 - 04 - 21" 이런 형식으로 써진 데이터를 "April 21 2017"로 바꾸려면 어떻게 해야할까
방법 1.
mysql> SELECT CONCAT(MONTHNAME(birthdt),' ',DAY(birthdt),' ',YEAR(birthdt)) FROM people;
+---------------------------------------------------------------+
| CONCAT(MONTHNAME(birthdt),' ',DAY(birthdt),' ',YEAR(birthdt)) |
+---------------------------------------------------------------+
| November 11 1983 |
| December 12 1943 |
| May 7 2021 |
+---------------------------------------------------------------+
3 rows in set (0.01 sec)더 간단한 방법 : DATE_FORMAT()사용.
DATE_FORMAT()
mysql> SELECT DATE_FORMAT(birthdt,'%M %d %Y') FROM people;
+---------------------------------+
| DATE_FORMAT(birthdt,'%M %d %Y') |
+---------------------------------+
| November 11 1983 |
| December 12 1943 |
| May 07 2021 |
+---------------------------------+
3 rows in set (0.00 sec)++DATE_FORMAT()을 이용하면 November 1st 1983 이런식으로 date 뒤에 st나 nd, rd를 붙이는 것도 간단히 해결된다.(1st, 2nd, 3rd...)
mysql> SELECT DATE_FORMAT('2021-05-01','%M %D %Y'),
DATE_FORMAT('2021-05-02','%M %D %Y'),
DATE_FORMAT('2021-05-03','%M %D %Y'),
DATE_FORMAT('2021-05-04','%M %D %Y'),
DATE_FORMAT('2021-05-05','%M %D %Y');
+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+
| DATE_FORMAT('2021-05-01','%M %D %Y') | DATE_FORMAT('2021-05-02','%M %D %Y') | DATE_FORMAT('2021-05-03','%M %D %Y') | DATE_FORMAT('2021-05-04','%M %D %Y') | DATE_FORMAT('2021-05-05','%M %D %Y') |
+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+
| May 1st 2021 | May 2nd 2021 | May 3rd 2021 | May 4th 2021 | May 5th 2021 |
+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)format들 사이에 문자를 넣어줄 수도 있다.
mysql> SELECT DATE_FORMAT('2021-05-05','%M-%D-%Y');
+--------------------------------------+
| DATE_FORMAT('2021-05-05','%M-%D-%Y') |
+--------------------------------------+
| May-5th-2021 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT('2021-05-05','%M월%D일%Y년도');
+------------------------------------------------+
| DATE_FORMAT('2021-05-05','%M월%D일%Y년도') |
+------------------------------------------------+
| May월5th일2021년도 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT('2021-05-05','%M월%D일%Y년도%a요일');
+--------------------------------------------------------+
| DATE_FORMAT('2021-05-05','%M월%D일%Y년도%a요일') |
+--------------------------------------------------------+
| May월5th일2021년도Wed요일 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT(birthdt, '%m/%d/%y at %h : %m') FROM people;
+---------------------------------------------+
| DATE_FORMAT(birthdt, '%m/%d/%y at %h : %m') |
+---------------------------------------------+
| 11/11/83 at 10 : 11 |
| 12/12/43 at 11 : 12 |
| 05/07/21 at 11 : 05 |
+---------------------------------------------+
3 rows in set (0.01 sec)
임재현입니다.