MySQL | EXTRACT method
Start your free 7-days trial now!
MySQL's EXTRACT(~) method extracts the specified portion of a date, time or datetime value.
Parameters
1. unit | unit
The unit of the portion to extract from the date, time or datetime.
2. date | date, time or datetime
The date, time or datetime to extract from.
Return value
The value for the specified unit in the input date, time or datetime.
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Basic usage
To extract the year from day_enrolled:
SELECT fname, EXTRACT(YEAR FROM day_enrolled)FROM students;
+----------+---------------------------------+| fname | EXTRACT(YEAR FROM day_enrolled) |+----------+---------------------------------+| Sky | 2015 || Ben | 2016 || Travis | 2018 || Arthur | 2016 || Benjamin | 2014 |+----------+---------------------------------+
To extract the hour and minute from a datetime:
SELECT EXTRACT(HOUR_MINUTE FROM '2020-04-26 19:55:40.000034');
+--------------------------------------------------------+| EXTRACT(HOUR_MINUTE FROM '2020-04-26 19:55:40.000034') |+--------------------------------------------------------+| 1955 |+--------------------------------------------------------+
List of Units
Unit | Expected Input Format |
|---|---|
MICROSECOND |
|
SECOND |
|
MINUTE |
|
HOUR |
|
DAY |
|
WEEK |
|
MONTH |
|
QUARTER |
|
YEAR |
|
SECOND_MICROSECOND |
|
MINUTE_MICROSECOND |
|
MINUTE_SECOND |
|
HOUR_MICROSECOND |
|
HOUR_SECOND |
|
HOUR_MINUTE |
|
DAY_MICROSECOND |
|
DAY_SECOND |
|
DAY_MINUTE |
|
DAY_HOUR |
|
YEAR_MONTH |
|
