MySQL EXTRACT() Function
The MySQL EXTRACT() function is used to extract parts from a date or datetime value.
Syntax
EXTRACT(unit FROM datetime)
Parameters
datetime |
Required. Specify a date or datetime value from which the parts is to be extracted. |
unit |
Required. Specify the unit type of interval to extract. It can be one of the following values:
|
Return Value
Returns the extracted part.
Example 1:
The example below shows the usage of EXTRACT() function.
mysql> SELECT EXTRACT(DAY FROM '2019-10-25'); Result: 25 mysql> SELECT EXTRACT(HOUR FROM '2019-10-25 08:10:25'); Result: 8 mysql> SELECT EXTRACT(MINUTE FROM '2019-10-25 08:10:25'); Result: 10 mysql> SELECT EXTRACT(SECOND FROM '2019-10-25 08:10:25'); Result: 25 mysql> SELECT EXTRACT(WEEK FROM '2019-10-25 08:10:25'); Result: 42 mysql> SELECT EXTRACT(MONTH FROM '2019-10-25'); Result: 10 mysql> SELECT EXTRACT(QUARTER FROM '2019-10-25'); Result: 4 mysql> SELECT EXTRACT(YEAR FROM '2019-10-25'); Result: 2019 mysql> SELECT EXTRACT(MICROSECOND FROM '2019-10-25 08:10:25.000123'); Result: 123 mysql> SELECT EXTRACT(SECOND_MICROSECOND FROM '2019-10-25 08:10:25.000123'); Result: 25000123 mysql> SELECT EXTRACT(MINUTE_MICROSECOND FROM '2019-10-25 08:10:25.000123'); Result: 1025000123 mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2019-10-25 08:10:25'); Result: 1025 mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2019-10-25 08:10:25.000123'); Result: 81025000123 mysql> SELECT EXTRACT(HOUR_SECOND FROM '2019-10-25 08:10:25'); Result: 81025 mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2019-10-25 08:10:25'); Result: 810 mysql> SELECT EXTRACT(DAY_MICROSECOND FROM '2019-10-25 08:10:25.000123'); Result: 25081025000123 mysql> SELECT EXTRACT(DAY_SECOND FROM '2019-10-25 08:10:25'); Result: 25081025 mysql> SELECT EXTRACT(DAY_MINUTE FROM '2019-10-25 08:10:25'); Result: 250810 mysql> SELECT EXTRACT(DAY_HOUR FROM '2019-10-25 08:10:25'); Result: 2508 mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-10-25 08:10:25'); Result: 201910
Example 2:
Consider a database table called EmployeeLogin with the following records:
EmpID | Name | LoginStamp |
---|---|---|
1 | John | 2019-10-25 09:20:38 |
2 | Marry | 2019-10-25 09:21:05 |
3 | Jo | 2019-10-25 09:24:35 |
4 | Kim | 2019-10-25 09:25:24 |
5 | Ramesh | 2019-10-25 09:27:16 |
6 | Suresh | 2019-10-25 09:28:19 |
To extract the HOUR_MINUTE part from the records of LoginStamp column, the following query can be used:
SELECT *, EXTRACT(HOUR_MINUTE FROM LoginStamp) AS EXTRACT_Value FROM EmployeeLogin;
This will produce a result similar to:
EmpID | Name | LoginStamp | EXTRACT_Value |
---|---|---|---|
1 | John | 2019-10-25 09:20:38 | 920 |
2 | Marry | 2019-10-25 09:21:05 | 921 |
3 | Jo | 2019-10-25 09:24:35 | 924 |
4 | Kim | 2019-10-25 09:25:24 | 925 |
5 | Ramesh | 2019-10-25 09:27:16 | 927 |
6 | Suresh | 2019-10-25 09:28:19 | 928 |
❮ MySQL Functions