MySQL EXTRACT Function

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

EXTRACT(Unit FROM Date);

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

MySQL EXTRACT Date Function Example

In this example, we are printing 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');
Get Date, Time, Hour, Minute Example 1

TIP: I suggest you refer to the Date method article in MySQL to understand the Units after the Interval with an example.

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

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');
Week, Day, and Hour Example 2

EXTRACT Time Function Example 3

Here, we are printing 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');
Get Minutes, Seconds, and Microseconds 3

date and Time Example 4

In this MySQL Extract function query, First, we printed the Year and Month as YYYYMM. The second statement gets Days, Hours, and Minutes as DDHHMM. The third statement gets 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