EXTRACT() and DAYOFYEAR() Function in MariaDB

1. EXTRACT() Function :

In MariaDB, the EXTRACT() function is used to return extracts the extracts parts from a date. In this function, the first parameter will be an expression. The first part of expression will be unit and second part of expression will be a date. This function will return extracts the unit part from the date. 

Syntax :

EXTRACT(unit FROM date)

Parameters :

• Unit - The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, etc.
• Date - A date or DateTime value from which the date part is to be extracted.

Returns : A unit part from a date.

Example-1 :

SELECT EXTRACT(SECOND FROM '2020-05-19 08:44:21');

Output :

21

Example-2 :

SELECT EXTRACT(YEAR_MONTH FROM '2010-05-19');

Output :

201005

Example-3 :

SELECT EXTRACT(MINUTE_MICROSECOND FROM '2014-05-19 08:44:21.000001');

Output :

4421000001

2. DAYOFYEAR() Function :

 In MariaDB, the DAYOFYEAR() function is used to return the day of the year for a date value. In this function, the first parameter will be a date_value.  This function will return a Day of Year y from the date passed as a parameter. This function returns the day of the year (a number from 1 to 366) given a date value.

Syntax :

DAYOFYEAR(date_value)

Parameter :

• Date - Date to which the Day of Year part will be extracted.

Returns : Day of Year from the date passed.

Example-1 :

SELECT DAYOFYEAR('2015-12-31');

Output :

365

Example-2 :

SELECT DAYOFYEAR('2018-05-20')

Output :

140

Example-3 :

SELECT DAYOFYEAR('2020-01-02')

Output :

2