MySql - 날짜 포맷 datetime to string 변환하기 (vs Oracle to_char 함수 비교 )

1. Overview

---

MySql은 날짜 포맷 변환을 위해 DATE_FORMAT 함수를 제공한다. DATE_FORMAT의 사용법을 간단한 예제를 통해 알아보고 oracle의 TO_CHAR함수와 어떤 차이가 있는지 알아본다.

 

2. How to use

---

DATE_FORMAT( date, format )

mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

2021-10-17

아래와 같이, date or datetime 형태를 갖는 String을 지정하여 사용할 수 있다.

mysql> SELECT DATE_FORMAT('2021-10-17 13:20:54', '%Y년 %m월 %d일 %H분');

2021년 10월 17일 13분

 

UNIX_TIMESTAMP to datetime

mysql> SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%Y-%m-%d %H:%i:%s');

2021-10-17 18:52:55

 

MySql 포맷 지정자

%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

 

3. MySql DATE_FORMAT VS Oracle TO_CHAR

---

		DATE_FORMAT	TO_CHAR
1	%Y	4-digit year	YYYY
2	%y	2-digit year, 20th century for 00-49	RR
3	%b	Abbreviated month (Jan - Dec)	MON
4	%M	Month name (January - December)	MONTH
5	%m	Month (0 - 12)	MM
6	%a	Abbreviated day (Sun - Sat)	DY
7	%d	Day (0 - 31)	DD
8	%H	Hour (0 - 23)	HH24
9	%h	Hour (1 - 12)	HH or HH12
10	%i	Minutes (0 - 59)	MI
11	%s	Seconds (0 - 59)	SS
12	%T	Time (hours, minutes and seconds)	HH24:MI:SS

출처: http://www.sqlines.com/mysql-to-oracle/date_format