MySQL EXTRACT()

MySQL 날짜함수, 시간함수

 

EXTRACT()

   -  날짜에서 년, 월, 일, 시간등을 추출

 

EXTRACT(unit FROM date)

의 형태

 

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

 

 

unit 으로 쓸 수 있는 것들

unit value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

 

mysql> SELECT EXTRACT(YEAR FROM '2020-02-02');
+---------------------------------+
| EXTRACT(YEAR FROM '2020-02-02') |
+---------------------------------+
|                            2020 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(DAY FROM NOW());
+-------------------------+
| EXTRACT(DAY FROM NOW()) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(MICROSECOND FROM '2009-01-01 :23:59:59.000123');
+---------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2009-01-01 :23:59:59.000123') |
+---------------------------------------------------------+
|                                                     123 |
+---------------------------------------------------------+
1 row in set (0.00 sec)