CENTURY

The century number.

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');

Result: 20

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 21

The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 to 1.

DAY

The day (of the month) field (1 - 31).

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 16

DECADE

The year field divided by 10.

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 200

DOW

The day of the week (0 - 6; Sunday is 0) (for timestamp values only).

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 5

Note that EXTRACT's day of the week numbering is different from that of the to_char function. doy

The day of the year (1 - 365/366) (for timestamp values only)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 47

EPOCH

For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 (can be negative); for interval values, the total number of seconds in the interval.

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');

Result: 982384720

 

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');

Result: 442800

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

HOUR

The hour field (0 - 23).

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 20

MICROSECONDS

The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes full seconds.

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');

Result: 28500000

MILLENNIUM

The millennium number.

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 3

Years in the 1900s are in the second millennium. The third millennium starts January 1, 2001.

Vertica releases before 8.0 did not follow the conventional numbering of millennia, but just returned the year field divided by 1000.

MILLISECONDS

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');

Result: 28500

MINUTE

The minutes field (0 - 59).

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 38

MONTH

For timestamp values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11).

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 2

 

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');

Result: 3

 

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');

Result: 1

QUARTER

The quarter of the year (1 - 4) that the day is in (for timestamp values only).

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 1

SECOND

The seconds field, including fractional parts (0 - 59) (60 if leap seconds are implemented by the operating system).

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 40

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');

Result: 28.5

TIMEZONE

The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.

TIMEZONE_HOUR

The hour component of the time zone offset.

TIMEZONE_MINUTE

The minute component of the time zone offset.

WEEK

The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only).

Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');

Result: 7

YEAR

The year field. Keep in mind there is no 0 AD, so subtracting BC years from AD years should be done with care.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2001