6
EXTRACT(field FROM source)
8
The EXTRACT function retrieves subfields such as day or hour from date/time values. The source value has to be a value expression of type *timestamp*, *time*, *date*, or *interval*.
10
*Field* is an identifier or string that identifies the field to extract from the source value. The extract function returns values of type *double precision*.
14
The following field names are available:
18
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
26
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
28
The year, divided by 10.
34
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
36
The day of the week (Sunday is 0, Saturday is 6)
42
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
44
The day of the year (1 - 365/366)
50
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
52
The hour field (0 - 23)
58
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
60
The seconds field, including fractional parts, multiplied by 1 000 000; note that this includes full seconds
66
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
68
The minutes field (0 - 59)
74
For timestamp values, the number of the month within the year (1 - 12).
75
For interval values, the number of months (0 - 11). ::
77
SELECT EXTRACT(MONTH FROM TIMESTAMP '2010-12-29 08:45:27');
81
SELECT EXTRACT(MONTH FROM INTERVAL '3 years 4 months');
85
SELECT EXTRACT(MONTH FROM INTERVAL '3 years 13 months');
91
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2010-12-29 08:45:27');
93
The quarter of the year (1 - 4) containing the date.
99
SELECT EXTRACT(SECOND FROM TIMESTAMP '2010-12-29 08:45:27');
101
The seconds field, including fractional parts (0 - 59)
105
SELECT EXTRACT(SECOND FROM TIME '08:15:22.5');
111
The time zone offset from UTC, measured in seconds.
115
Returns the week number that a day is in. Weeks are numbered according to ISO 8601:1988.
117
ISO 8601:1988 means that if the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is the last week of the previous year, and the next week is week 1. The ISO-8601 week starts on Monday.
119
It's possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2011-01-01 was part of the 52nd week of year 2010. ::
121
SELECT EXTRACT(WEEK FROM TIMESTAMP '2010-01-25 12:44:06');
127
SELECT EXTRACT(YEAR FROM TIMESTAMP '2009-02-16 20:38:40');
131
The valid field names for date_part are the same as for extract. ::
133
SELECT date_part('day', TIMESTAMP '2010-07-16 10:12:05');
137
SELECT date_part('hour', INTERVAL '5 hours 12 minutes');