PostgreSQL: extract Function

In this PostgreSQL post explains how to use the PostgreSQL extract function with syntax and examples.

Description

The PostgreSQL extract function extracts parts from a date.

Syntax

The syntax for the extract function in PostgreSQL is:

extract( unit from date )

Parameters or Arguments

date

T date, timestamp, time, or interval value from which the date part is to be extracted.

unit

The unit type of the interval such as day, month, minute, hour, and so on. It can be one of the following:

unit	Explanation
century	Uses the Gregorian calendar where the first century starts at '0001-01-01 00:00:00 AD'
day	Day of the month (1 to 31)
decade	Year divided by 10
dow	Day of the week (0=Sunday, 1=Monday, 2=Tuesday, ... 6=Saturday)
doy	Day of the year (1=first day of year, 365/366=last day of the year, depending if it is a leap year)
epoch	Number of seconds since '1970-01-01 00:00:00 UTC', if date value. Number of seconds in an interval, if interval value
hour	Hour (0 to 23)
isodow	Day of the week (1=Monday, 2=Tuesday, 3=Wednesday, ... 7=Sunday)
isoyear	ISO 8601 year value (where the year begins on the Monday of the week that contains January 4th)
microseconds	Seconds (and fractional seconds) multiplied by 1,000,000
millennium	Millennium value
milliseconds	Seconds (and fractional seconds) multiplied by 1,000
minute	Minute (0 to 59)
month	Number for the month (1 to 12), if date value. Number of months (0 to 11), if interval value
quarter	Quarter (1 to 4)
second	Seconds (and fractional seconds)
timezone	Time zone offset from UTC, expressed in seconds
timezone_hour	Hour portion of the time zone offset from UTC
timezone_minute	Minute portion of the time zone offset from UTC
week	Number of the week of the year based on ISO 8601 (where the year begins on the Monday of the week that contains January 4th)
year	Year as 4-digits

Note

• See also the date_part function.

Applies To

The extract function can be used in the following versions of PostgreSQL:

• PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4

Example

Let's look at some PostgreSQL extract function examples and explore how to use the extract function in PostgreSQL with date values.

For example:

postgres=# SELECT extract(day from date '2014-04-25');
 date_part
-----------

        25
(1 row)

postgres=# SELECT extract(month from date '2014-04-25');
 date_part
-----------

         4
(1 row)

postgres=# SELECT extract(year from date '2014-04-25');
 date_part
-----------

      2014
(1 row)

Let's explore how to use the extract function in PostgreSQL with timestamp values.

For example:

postgres=# SELECT extract(day from timestamp '2014-04-25 08:44:21');
 date_part
-----------

        25
(1 row)

postgres=# SELECT extract(month from timestamp '2014-04-25 08:44:21');
 date_part
-----------

         4
(1 row)

postgres=# SELECT extract(minute from timestamp '2014-04-25 08:44:21');
 date_part
-----------

        44
(1 row)

postgres=# SELECT extract(hour from timestamp '2014-04-25 08:44:21');
 date_part
-----------

         8
(1 row)

Let's explore how to use the extract function in PostgreSQL with time values.

For example:

postgres=# SELECT extract(minute from time '08:44:21');
 date_part
-----------

        44
(1 row)

postgres=# SELECT extract(milliseconds from time '08:44:21.7');
 date_part
-----------

     21700
(1 row)

Let's explore how to use the extract function in PostgreSQL with interval values.

For example:

postgres=# SELECT extract(day from interval '5 days 3 hours');
 date_part
-----------

         5
(1 row)

postgres=# SELECT extract(hour from interval '5 days 3 hours');
 date_part
-----------

         3
(1 row)