Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443