SQL in a Nutshell, 3rd Edition

Name

EXTRACT

The ANSI SQL scalar function for extracting parts from a date is EXTRACT.

ANSI SQL Standard Syntax

The ANSI SQL EXTRACT function takes a date_part and an expression that evaluates to a datetime value. MySQL, Oracle, and PostgreSQL support the ANSI SQL standard syntax:

EXTRACT( date_part FROM expression )

MySQL

MySQL’s implementation extends somewhat beyond the ANSI standard. The ANSI standard does not have a provision for returning multiple fields from 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
MICROSECOND	Microseconds
SECOND	Seconds
MINUTE	Minutes
HOUR	Hours
DAY	Days
WEEK	Weeks
MONTH	Months
QUARTER	Quarter
YEAR	Years
SECOND_MICROSECOND	Seconds and microseconds
MINUTE_MICROSECOND	Minutes and microseconds
MINUTE_SECOND	Minutes and seconds
HOUR_MICROSECOND	Hours and microseconds
HOUR_SECOND	Hours, minutes, and seconds
HOUR_MINUTE	Hours and minutes
DAY_MICROSECOND	Days and microseconds
DAY_SECOND	Days, hours, minutes, and seconds
DAY_MINUTE	Days, hours, and minutes
DAY_HOUR	Days and hours
YEAR_MONTH	Years and months

Oracle

Oracle supports the ANSI SQL syntax with the dateparts listed in Table 4-6.

Table 4-6. Oracle dateparts

Type value	Meaning
DAY	The day of the month field (1–31)
HOUR	The hour field (0–23)
MINUTE	The minutes field (0–59)
MONTH	The month field (1–12)
SECOND	The seconds field (0–59)
TIMEZONE_HOUR	The hour component of the ...