PostgreSQL: date_part Function

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

Description

The PostgreSQL date_part function extracts parts from a date.

Syntax

The syntax for the date_part function in PostgreSQL is:

date_part( 'unit', date )

Parameters or Arguments

date

The 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 extract function.

Applies To

The date_part 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 date_part function examples and explore how to use the date_part function in PostgreSQL with date values.

For example:

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

        25
(1 row)

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

         4
(1 row)

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

      2014
(1 row)

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

For example:

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

        25
(1 row)

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

         4
(1 row)

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

        44
(1 row)

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

         8
(1 row)

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

For example:

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

        44
(1 row)

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

     21700
(1 row)

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

For example:

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

         5
(1 row)

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

         3
(1 row)