Name
EXTRACT
Synopsis
The EXTRACT function is not supported by the database vendors, except for PostgreSQL and MySQL.
Each vendor supports a separate command to accomplish the same functionality. Oracle uses the TO_CHAR function to extract a portion of a date into a character string. SQL Server uses the CONVERT function to extract a portion of a date.
MySQL implementation is extended somewhat beyond the SQL99 standard. The SQL99 standard does not have a provision for returning multiple fields in the same call to EXTRACT( ) (e.g., “DAY_HOUR”). The MySQL extensions try to accomplish what the combination DATE_TRUNC( ) and DATE_PART( ) do in PostgreSQL. MySQL supports the dateparts listed in Table 4.5.
Table 4-5. MySQL Dateparts
Type value |
Meaning |
Expected format |
---|---|---|
SECOND |
Seconds |
SECONDS |
MINUTE |
Minutes |
MINUTES |
HOUR |
Hours |
HOURS |
DAY |
Days |
DAYS |
MONTH |
Months |
MONTHS |
YEAR |
Years |
YEARS |
MINUTE_SECOND |
Minutes and seconds |
“MINUTES:SECONDS” |
HOUR_MINUTE |
Hours and minutes |
“HOURS:MINUTES” |
DAY_HOUR |
Days and hours |
“DAYS HOURS” |
YEAR_MONTH |
Years and months |
“YEARS-MONTHS” |
HOUR_SECOND |
Hours, minutes, seconds |
“HOURS:MINUTES:SECONDS” |
DAY_MINUTE |
Days, hours, minutes |
“DAYS HOURS:MINUTES” |
DAY_SECOND |
Days, hours, minutes, seconds |
“DAYSHOURS:MINUTES:SECONDS” |
Example
This example extracts dateparts from several datetime values:
/* On MySQL */ SELECT EXTRACT(YEAR FROM "2013-07-02"); -> 1999 SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03"); -> 199907 SELECT EXTRACT(DAY_MINUTE ...
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.