EXTRACT

century

Century.

The first century begins at 0001-01-01 00:00:00 AD. This definition applies to all countries using the Gregorian calendar. There is no 0th century. It goes directly from the 1st century BC to the 1st century AD.

Example:

1
2
3
4
5

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

day

• If source is timestamp, it indicates the day of the month (1–31).

  1 2 3 4 5

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

• If source is interval, it indicates the number of days.

  1 2 3 4 5

  SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); date_part ----------- 40 (1 row)

decade

Year divided by 10.

1
2
3
4
5

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

dow

Day of the week, ranging from Sunday (0) to Saturday (6).

1
2
3
4
5

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

doy

Day of the year (1 to 365 or 366).

1
2
3
4
5

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

epoch

• If source is timestamp with time zone, it indicates the number of seconds since 1970-01-01 00:00:00-00 UTC (result could be negative).

  If source is date and timestamp, it indicates the number of seconds since 1970-01-01 00:00:00-00 local time.

  If source is interval, it indicates the total number of seconds in the interval.

  1 2 3 4 5

  SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); date_part -------------- 982384720.12 (1 row)

  1 2 3 4 5

  SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); date_part ----------- 442800 (1 row)

• Method to convert an epoch value to a timestamp.

  1 2 3 4 5

  SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second' AS RESULT; result --------------------------- 2001-02-17 12:38:40.12+08 (1 row)

hour

Hour column (0–23).

1
2
3
4
5

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

isodow

Day of the week (1–7).

Monday is 1, and Sunday is 7.

This is identical to dow except for Sunday.

1
2
3
4
5

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
 date_part 
-----------
         7
(1 row)

isoyear

ISO 8601 standard year in the date (not applicable to intervals).

Every week starting on Monday containing January 4 falls within the ISO year, so the ISO year in early January or late December might differ from the Gregorian year. See the subsequent week description for more details.

1
2
3
4
5

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
 date_part 
-----------
      2005
(1 row)

1
2
3
4
5

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
 date_part 
-----------
      2006
(1 row)

microseconds

Seconds column (including fractional parts) multiplied by 1,000,000.

1
2
3
4
5

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
 date_part 
-----------
  28500000
(1 row)

millennium

Millennium.

Years in the 20th century (19xx) fall within the second millennium. The third millennium began at midnight on January 1, 2001.

1
2
3
4
5

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

milliseconds

Seconds column (including fractional parts) multiplied by 1,000. Note that it includes whole seconds.

1
2
3
4
5

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
 date_part 
-----------
     28500
(1 row)

minute

Minutes column (0–59).

1
2
3
4
5

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

month

If source is timestamp, it indicates the month of the year (1–12).

1
2
3
4
5

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

If source is interval, it indicates the number of months, then modulo 12 (0–11).

1
2
3
4
5

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
 date_part 
-----------
         1
(1 row)

quarter

The quarter of the year in which the day falls (1–4).

1
2
3
4
5

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

second

Seconds column, including fractional parts (0–59).

1
2
3
4
5

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
 date_part 
-----------
      28.5
(1 row)

timezone

Timezone offset from UTC in seconds. Positive values correspond to timezones east of UTC, negative values to those west of UTC.

1
2
3
4
5

SELECT EXTRACT(timezone FROM TIMETZ '17:12:28');
 date_part
-----------
   28800
(1 row)

timezone_hour

Hours portion of the timezone offset.

1
2
3
4
5

SELECT EXTRACT(timezone_hour FROM TIMETZ '17:12:28');
 date_part
-----------
       8
(1 row)

timezone_minute

Minutes portion of the timezone offset.

1
2
3
4
5

SELECT EXTRACT(timezone_minute FROM TIMETZ '17:12:28');
 date_part
-----------
         0
(1 row)

week

The week number of the year in which the day falls. ISO 8601 defines the first week of the year as containing January 4 (ISO-8601 weeks start on Monday). In other words, the first Thursday of the year falls in the first week.

Under ISO definitions, the first few days of January might belong to the 52nd or 53rd week of the previous year, and the last few days of December might belong to the first week of the following year. For example, 2005-01-01 was the 53rd week of 2004, while 2006-01-01 was the 52nd week of 2005, and 2012-12-31 was the first week of 2013. You are advised to use the isoyear column along with week to achieve consistent results.

1
2
3
4
5

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

year

Year column.

1
2
3
4
5

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