MySQL EXTRACT() function

EXTRACT() function

MySQL EXTRACT() EXTRACTs a part of a given date. This function does not perform date arithmetic. The unit specifiers of DATE_ADD() and DATE_SUB() work with this function also. It provides a versatile way to retrieve individual parts of a date or datetime value.

This function is useful in -

• EXTRACT() is used to retrieve individual components for analysis, allowing you to understand and analyze patterns based on specific parts of the date.
• For seasonal analysis or event tracking, EXTRACT() aids in isolating specific components (such as the month) to identify trends or patterns.
• EXTRACT() helps retrieve historical data based on specific components, enabling you to compare data across different years, months, etc.
• EXTRACT() can be used to transform dates or datetimes into custom formats, by extracting and combining specific components.
• EXTRACT() enables various calculations that involve date components, such as finding the number of days between two dates or the difference in hours.
• In graphical representations, EXTRACT() can be used to label axes or data points with specific date components, enhancing clarity.

Syntax:

EXTRACT(unit FROM date1)

Where date1 is a date.

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL EXTRACT() function

The following statement will EXTRACT the year part from the date 2008-05-15.

Code:

SELECT EXTRACT(YEAR FROM '2008-05-15');

Output:

mysql> SELECT EXTRACT(YEAR FROM '2008-05-15');
+---------------------------------+
| EXTRACT(YEAR FROM '2008-05-15') |
+---------------------------------+
|                            2008 | 
+---------------------------------+
1 row in set (0.00 sec)

Example : EXTRACT() function using HOUR_SECOND

The following statement will EXTRACT the HOUR_SECOND part from 2008-05-15 15:53:20.

Code:

SELECT EXTRACT(HOUR_SECOND FROM '2008-05-15 15:53:20');

Output:

mysql> SELECT EXTRACT(HOUR_SECOND FROM '2008-05-15 15:53:20');
+-------------------------------------------------+
| EXTRACT(HOUR_SECOND FROM '2008-05-15 15:53:20') |
+-------------------------------------------------+
|                                          155320 | 
+-------------------------------------------------+
1 row in set (0.00 sec)

Example : EXTRACT() function using YEAR_MONTH

The following statement will EXTRACT the year and month part from 2008-05-15 15:53:20.

Code:

SELECT EXTRACT(YEAR_MONTH FROM '2008-05-15 15:53:20');

Output:

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2008-05-15 15:53:20');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2008-05-15 15:53:20') |
+------------------------------------------------+
|                                         200805 | 
+------------------------------------------------+
1 row in set (0.00 sec)

Example : EXTRACT() function using YEAR FROM

The following statement will EXTRACT the year part from the datetime 2008-05-15 15:53:20.

Code:

SELECT EXTRACT(YEAR  FROM '2008-05-15 15:53:20');

Output:

 mysql> SELECT EXTRACT(YEAR  FROM '2008-05-15 15:53:20');
+-------------------------------------------+
| EXTRACT(YEAR  FROM '2008-05-15 15:53:20') |
+-------------------------------------------+
|                                      2008 | 
+-------------------------------------------+
1 row in set (0.00 sec)

View the example in browser

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: DAY OF YEAR()
Next: FROM_DAYS()