MySQL EXTRACT Function

MySQL EXTRACT function is one of the Date Functions, which is useful to extracts parts from a given DateTime. Using this function, you can extract the Dates and Time values. The basic syntax of the EXTRACT Function in MySQL is as shown below:

EXTRACT(Unit FROM Date);

MySQL EXTRACT Examples

The following are the list examples that help you understand the use of this Date and Time extract function.

MySQL EXTRACT Date Function Example 1

In this example, we are extracting the Year, Quarter, and Month Number from a given DateTime expression.

SELECT EXTRACT(YEAR FROM '2018-08-31');

SELECT EXTRACT(QUARTER FROM '2018-08-31');

SELECT EXTRACT(MONTH FROM '2018-08-31');
MySQL EXTRACT Function 1

TIP: I suggest you refer to the Date Function article in MySQL to understand the Units after the Interval.

EXTRACT Date Function Example 2

In this example, First, we are extorting the Week Number. Next, we extracted the Day Number from a given Datetime expression. Within the third statement, we extracted the Hour value.

SELECT EXTRACT(WEEK FROM '2018-04-30 23:30:15');

SELECT EXTRACT(DAY FROM '2018-04-30 23:30:15');

SELECT EXTRACT(HOUR FROM '2018-04-30 23:30:15');
MySQL EXTRACT Function 2

MySQL EXTRACT Time Function Example 3

Here, we are extracting Minutes, Seconds, and Microseconds from the given DateTime 2018-04-30 23:30:15.999888.

SELECT EXTRACT(MINUTE FROM '2018-04-30 23:30:15.999888');

SELECT EXTRACT(SECOND FROM '2018-04-30 23:30:15.999888');

SELECT EXTRACT(MICROSECOND FROM '2018-04-30 23:30:15.999888');
MySQL Date EXTRACT Function 3

MySQL EXTRACT date and Time Example 4

In this example, First, we extracted the Year and Month as YYYYMM. The second statement extracts Days, Hours, and Minutes as DDHHMM. The third statement extracts Days, Hours, Minutes, and Seconds as DDHHMMSS from the given DateTime value.

SELECT EXTRACT(YEAR_MONTH FROM '2019-02-28 23:30:15.999888');

SELECT EXTRACT(DAY_MINUTE FROM '2019-02-28 23:30:15.999888');

SELECT EXTRACT(DAY_SECOND FROM '2019-02-28 23:30:15.999888');
MySQL EXTRACT Date Function 4