EXTRACT

MySQL

EXTRACT(unit FROM date)

• unit: This parameter indicates the part of the date or time to extract. Some of the valid unit values include YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEK, QUARTER, DAYOFWEEK, DAYOFYEAR, etc.
• date: This denotes the date or datetime expression from which to extract a part. It can be a column in a table, a string literal, or a result of a function that returns date or datetime. The value can be a DATE, DATETIME, TIME, YEAR, TIMESTAMP data type or a numeric or string type that represents a date or datetime.

Example

SELECT EXTRACT(YEAR FROM '2022-03-01');

Output

2022

Explanation

The EXTRACT function in MySQL is used to return a specific component from a date/datetime expression.

In the given example, EXTRACT function is extracting the YEAR from the date ‘2022-03-01’. The output returns ‘2022’ which is the extracted year.

PostgreSQL

EXTRACT(field FROM source)

• field: This parameter specifies the part of the date or time value that should be extracted. Options include year, month, day, hour, minute, second, decade, century, millennium, dow (day of the week), doy (day of the year), epoch (the number of seconds since 1970-01-01 00
• source: This parameter in EXTRACT() function is the input date or time value from which the field parameter will be extracted. It can be a literal string, a variable, or a column of a table of types timestamp, interval, or time.

Example

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');

Output

2001

Explanation

In the above example, the EXTRACT function is used to retrieve the year from the provided timestamp ‘2001-02-16 20:38:40’. The output of the function is ‘2001’, which is the year part of the timestamp.

Oracle

EXTRACT(field FROM source)

• field: Specifies the part of the date/time value to retrieve. The possible values are YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR, ISOYEAR, ISOWEEK, and JULIAN_DAY.
• source: An expression which yields a value of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND. Field values are extracted from this source.

Example

SELECT EXTRACT(YEAR FROM TO_DATE('22-APR-2020','DD-MON-YYYY')) "Year" FROM dual;

Output

Year
2020

Explanation

The EXTRACT function in Oracle is used to get a specific part, like year, month, or day from a date. In the example, the EXTRACT function is used to extract the year from the date ‘22-APR-2020’.