How the EXTRACT() function works in Mariadb?

The EXTRACT() function is a date and time function that extracts a specific part of a date or time value.

Posted on

The EXTRACT() function is a date and time function that extracts a specific part of a date or time value. For example, you can use the EXTRACT() function to get the year, month, day, hour, minute, second, or other components of a date or time value.

Syntax

The syntax of the EXTRACT() function is as follows:

EXTRACT(unit FROM date)

The function takes two arguments: the unit and the date. The unit specifies which part of the date or time value you want to extract. The date is a valid date or time value. The function returns an integer value that represents the extracted part of the date or time value. If the date is invalid or the unit is not recognized, the function returns NULL.

The unit can be one of the following values:

  • MICROSECOND: The microsecond part of the date or time value, ranging from 0 to 999999.
  • SECOND: The second part of the date or time value, ranging from 0 to 59.
  • MINUTE: The minute part of the date or time value, ranging from 0 to 59.
  • HOUR: The hour part of the date or time value, ranging from 0 to 23.
  • DAY: The day part of the date or time value, ranging from 1 to 31.
  • WEEK: The week number of the year for the date or time value, ranging from 1 to 53. The week starts on Monday and follows the ISO 8601 standard.
  • MONTH: The month part of the date or time value, ranging from 1 to 12.
  • QUARTER: The quarter of the year for the date or time value, ranging from 1 to 4.
  • YEAR: The year part of the date or time value, ranging from 1000 to 9999.
  • SECOND_MICROSECOND: The second and microsecond parts of the date or time value, formatted as SS.ssssss.
  • MINUTE_MICROSECOND: The minute and microsecond parts of the date or time value, formatted as MM:SS.ssssss.
  • MINUTE_SECOND: The minute and second parts of the date or time value, formatted as MM:SS.
  • HOUR_MICROSECOND: The hour and microsecond parts of the date or time value, formatted as HH:MM:SS.ssssss.
  • HOUR_SECOND: The hour and second parts of the date or time value, formatted as HH:MM:SS.
  • HOUR_MINUTE: The hour and minute parts of the date or time value, formatted as HH:MM.
  • DAY_MICROSECOND: The day and microsecond parts of the date or time value, formatted as DD HH:MM:SS.ssssss.
  • DAY_SECOND: The day and second parts of the date or time value, formatted as DD HH:MM:SS.
  • DAY_MINUTE: The day and minute parts of the date or time value, formatted as DD HH:MM.
  • DAY_HOUR: The day and hour parts of the date or time value, formatted as DD HH.
  • YEAR_MONTH: The year and month parts of the date or time value, formatted as YYYY-MM.

Examples

Example 1: Extract the year from a date

In this example, we use the EXTRACT() function to get the year part of a date value. We use the CURDATE() function to get the current date.

SELECT EXTRACT(YEAR FROM CURDATE()) AS Year;

The output is:

+------+
| Year |
+------+
| 2024 |
+------+

The function returns the year part of the current date, which is 2024.

Example 2: Extract the hour and minute from a time

In this example, we use the EXTRACT() function to get the hour and minute parts of a time value. We use the CURTIME() function to get the current time.

SELECT EXTRACT(HOUR_MINUTE FROM CURTIME()) AS HourMinute;

The output is:

+------------+
| HourMinute |
+------------+
|       1307 |
+------------+

The function returns the hour and minute parts of the current time, formatted as HHMM. The current time is 13:07.

Example 3: Extract the week number from a date

In this example, we use the EXTRACT() function to get the week number of the year for a date value. We use the STR_TO_DATE() function to convert a string to a date value.

SELECT EXTRACT(WEEK FROM STR_TO_DATE('2024-01-23', '%Y-%m-%d')) AS Week;

The output is:

+------+
| Week |
+------+
|    3 |
+------+

The function returns the week number of the year for the date value, which is 3. The date value is 2024-01-23, which is the fourth week of the year according to the ISO 8601 standard.

Example 4: Extract the second and microsecond from a datetime

In this example, we use the EXTRACT() function to get the second and microsecond parts of a datetime value. We use the NOW() function to get the current datetime.

SELECT EXTRACT(SECOND_MICROSECOND FROM NOW()) AS SecondMicrosecond;

The output is:

+-------------------+
| SecondMicrosecond |
+-------------------+
|          16000000 |
+-------------------+

The function returns the second and microsecond parts of the current datetime, formatted as SS.ssssss. The current datetime is 2024-01-23 21:30:36.123456.

Example 5: Extract the year and month from a timestamp

In this example, we use the EXTRACT() function to get the year and month parts of a timestamp value. We use the CURRENT_TIMESTAMP() function to get the current timestamp.

SELECT EXTRACT(YEAR_MONTH FROM CURRENT_TIMESTAMP()) AS YearMonth;

The output is:

+-----------+
| YearMonth |
+-----------+
|    202403 |
+-----------+

The function returns the year and month parts of the current timestamp, formatted as YYYYMM. The current timestamp is 2024-03-17 21:30:36.

There are some other date and time functions that are related to the EXTRACT() function. Here are some of them:

  • DATE_FORMAT(date, format): This function formats a date or time value according to a specified format string. The format string can contain any of the date and time specifiers that are supported by the EXTRACT() function.

  • DATE_ADD(date, interval): This function adds a specified time interval to a date or time value. The interval can be any of the units that are supported by the EXTRACT() function.

  • DATE_SUB(date, interval): This function subtracts a specified time interval from a date or time value. The interval can be any of the units that are supported by the EXTRACT() function.

Conclusion

The EXTRACT() function is a useful date and time function that can extract a specific part of a date or time value. The function takes two arguments: the unit and the date. The unit specifies which part of the date or time value you want to extract. The date is a valid date or time value. The function returns an integer value that represents the extracted part of the date or time value, or NULL if the date is invalid or the unit is not recognized. The function supports various units, such as year, month, day, hour, minute, second, microsecond, and their combinations. There are some other date and time functions that are related to the EXTRACT() function, such as DATE_FORMAT(), DATE_ADD(),