SQL Pocket Guide, 3rd Edition

EXTRACT Function

DB2 (9.7 and higher), MySQL, Oracle, and PostgreSQL support the standard EXTRACT function to retrieve specific elements from a datetime value. In MySQL, for example:

SELECT EXTRACT(DAY FROM CURRENT_DATE);

The result will be a number. Valid elements are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR.

Oracle supports the following additional elements: TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR. The latter two Oracle elements are exceptions and return string values.

PostgreSQL also supports additional elements: CENTURY, DECADE, DOW (day of week), DOY (day of year), EPOCH (number of seconds in an interval, or since 1-Jan-1970 for a date), MICROSECONDS, MILLENNIUM, MILLISECONDS, QUARTER, TIMEZONE (offset from UTC, in seconds), TIMEZONE_HOUR (hour part of UTC offset), TIMEZONE_MINUTE (minute part of offset), and WEEK.