SQL in a Nutshell

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 ...