O'Reilly logo

SQL in a Nutshell, 3rd Edition 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

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

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