Postgresql Date_part Function

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

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)