D/MySQL

MySQL EXTRACT()

yeon.Biju 2020. 4. 1. 16:40

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)