UTC Extract

UTC_EXTRACT(unit FROM date)

The UTC_EXTRACT(unit FROM date) function extracts parts from a date and returns them as an integer in a specified format. The function is modeled after EXTRACT(), but differs in that it uses UTC for all of its calculations.

The date argument may be a DATE string, a DATETIME string, or a column of type DATE, DATETIME, or TIMESTAMP. If the date value is a string, UTC_EXTRACT interprets the date as a value in UTC.

The supported unit specifiers are YEAR_MONTH_DAY and YEAR_MONTH_DAY_HOUR, which returns a representation of the date argument as a value in the YYYYMMDD or YYYYMMDDHH format.

This function is intended to facilitate partitioning by days, specially when applied to TIMESTAMP values. It allows partitions to be named in a more human-readable format like YYYYMMDD.

mysql> SELECT UTC_EXTRACT(YEAR_MONTH_DAY FROM '2012-06-08') AS `YEAR_MONTH_DAY`;
+----------------+
| YEAR_MONTH_DAY |
+----------------+
|       20120608 |
+----------------+

mysql> SELECT UTC_EXTRACT(YEAR_MONTH_DAY_HOUR FROM '2012-06-08 18:54:02') AS `YEAR_MONTH_DAY_HOUR`;
+---------------------+
| YEAR_MONTH_DAY_HOUR |
+---------------------+
|          2012060818 |
+---------------------+