[MySQL] Date and Time Functions

SQL 문제 풀다가 자주 쓰는 date 함수를 때마다 찾아봤는데 비슷한게 많아서 정리해보았다.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofmonth

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

 

1. ADDDATE(date, INTERVAL expr unit), ADDDATE(date, days)

- 두 번째 인수에 INTERVAL 형식으로 호출될 때 DATE_ADD()와 같음.

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'

- 두 번째 인수에 days 형식으로 호출할 때 추가할 일 수를 정수로 처리하여 더한 날짜를 반환해줌.

SELECT ADDDATE('2008-01-02', 31);
        -> '2008-02-02'

 

2. DATE(expr)

- expr의 날짜 부분 추출

SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

 

3. DATEDIFF(expr1, expr2)

- expr1과 expr2의 날짜의 차이를 반환

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31

 

4. DAY(date) = DAYOFMONTH(date)

- 1~31사이의 날짜를 반환

SELECT DAYOFMONTH('2007-02-03');
        -> 3

 

5. DAYOFWEEK(date)

- 1 : 일요일, 2 : 월요일, 3 : 화요일, ... , 7 : 토요일의 요일 인덱스 반환 (ODBC 표준)

SELECT DAYOFWEEK('2007-02-03');
        -> 7

 

6. MONTH(date)

- 1~12월까지의 월을 반환

SELECT MONTH('2008-02-03');
        -> 2

 

7. YEAR(date)

- 1000 ~ 9999년 사이의 년을 반환

SELECT YEAR('1987-01-01');
        -> 1987

 

8. DATE_FORMAT(date, format)

- 형식 문자열에 따라 날짜 값의 형식을 지정

SpecifierDescription

%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
SELECT DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'