MySQL EXTRACT() Function

 The EXTRACT() function in MySQL is used to retrieve a portion of a date or datetime value based on the specified unit. It is commonly used to extract components such as the year, month, day, hour, minute, or second from date or datetime values.

Syntax

EXTRACT(unit FROM date)

Parameters

• unit: Specifies the part of the date/datetime to extract. Common values include:
  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • YEAR_MONTH (combines year and month)
  • DAY_HOUR, DAY_MINUTE, DAY_SECOND (combinations of day and time units)
  • HOUR_MINUTE, HOUR_SECOND (combinations of hour and smaller units)
  • MINUTE_SECOND
• date: The date or datetime value from which to extract the specified component.

Examples

Extracting Year

SELECT EXTRACT(YEAR FROM '2024-12-23');
-- Output: 2024

Extracting Month

SELECT EXTRACT(MONTH FROM '2024-12-23');
-- Output: 12

Extracting Day

SELECT EXTRACT(DAY FROM '2024-12-23');
-- Output: 23

Extracting Time Components

SELECT EXTRACT(HOUR FROM '2024-12-23 14:35:50');
-- Output: 14

SELECT EXTRACT(MINUTE FROM '2024-12-23 14:35:50');
-- Output: 35

SELECT EXTRACT(SECOND FROM '2024-12-23 14:35:50');
-- Output: 50

Extracting Combined Units

SELECT EXTRACT(YEAR_MONTH FROM '2024-12-23');
-- Output: 202412

SELECT EXTRACT(DAY_HOUR FROM '2024-12-23 14:35:50');
-- Output: 2314

Notes

• The EXTRACT() function is ANSI SQL-compliant and can be used interchangeably with other MySQL date and time functions.
• It is useful for breaking down date and time values for further analysis or querying.

Let me know if you'd like additional examples or details!