What composite units can be used in the MySQL EXTRACT() function?

MySQL EXTRACT() function can use the following compound units -

• SECOND_MICROSECOND
• MINUTE_MICROSECOND
HOUR_MICROSECOND
• DAY_MICROSECOND
• MINUTE_SECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Some examples of these compound units used in the EXTRACT() function are as follows -

mysql> Select EXTRACT(YEAR_MONTH from '2017-10-20');
+---------------------------------------+
| EXTRACT(YEAR_MONTH from '2017-10-20') |
+---------------------------------------+
|                             201710    |
+---------------------------------------+
1 row in set (0.00 sec)

The above query will return the year and month values ​​for that date.

mysql> Select EXTRACT(DAY_HOUR from '2017-10-20 05:46:45');
+----------------------------------------------+
| EXTRACT(DAY_HOUR from '2017-10-20 05:46:45') |
+----------------------------------------------+
|                                         2005 |
+----------------------------------------------+
1 row in set (0.00 sec)

The above query will return the date and hour value in the date.

We must take into account that the above composite unit is a complete set of values, i.e. if we will use DAY_MINUTE, MySQL returns the day, hour and minute. This means that any value normally expected will be filled in between the start and end units.

For example, the following query uses the DAY_MICROSECOND composite unit, and MySQL returns DAY, HOUR, MINUTE, SECOND, and microseconds. This means that the values ​​for HOUR, MINUTE, and SECOND are populated between DAY and MICROSECOND.

mysql> Select EXTRACT(DAY_MICROSECOND from '2017-10-22 05:52:45.102356');
+---------------------------------------------------------------------------+
| EXTRACT(DAY_MICROSECOND from '2017-10-22 05:52:45.102356')                |
+---------------------------------------------------------------------------+
|                                                            22055245102356 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

The above is the detailed content of What composite units can be used in the MySQL EXTRACT() function?. For more information, please follow other related articles on the PHP Chinese website!