O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required