PostgreSQL Tutorial: DATE_PART Function

Summary: In this tutorial, we will introduce you to the PostgreSQL DATE_PART() function that allows you to retrieve subfields e.g., year, month, week from a date or time value.

Table of Contents

Introduction to the PostgreSQL DATE_PART function

The DATE_PART() function extracts a subfield from a date or time value. The following illustrates the DATE_PART() function:

DATE_PART(field, source)

The field is an identifier that determines what field to extract from the source. The values of the field must be in a list of permitted values mentioned below:

• century
• decade
• year
• month
• day
• hour
• minute
• second
• microseconds
• milliseconds
• dow
• doy
• epoch
• isodow
• isoyear
• timezone
• timezone_hour
• timezone_minute

The source is a temporal expression that evaluates to TIMESTAMP, TIME, or INTERVAL. If the source evaluates to DATE, the function will cast to TIMESTAMP.

The DATE_PART() function returns a value whose type is double precision.

PostgreSQL DATE_PART examples

The following example extracts the century from a time stamp:

SELECT date_part('century', TIMESTAMP '2017-01-01');

 date_part
-----------
        21
(1 row)

To extract the year from the same timestamp, you pass the year to the field argument:

SELECT date_part('year', TIMESTAMP '2017-01-01');

 date_part
-----------
      2017
(1 row)

To extract the quarter, you use the following statement:

SELECT date_part('quarter', TIMESTAMP '2017-01-01');

 date_part
-----------
         1
(1 row)

To get the month, you pass the month to the DATE_PART() function:

SELECT date_part('month', TIMESTAMP '2017-09-30');

 date_part
-----------
         9
(1 row)

To get the decade from a time stamp, you use the statement below:

SELECT date_part('decade', TIMESTAMP '2017-09-30');

 date_part
-----------
       201
(1 row)

To extract the week number from a time stamp, you pass the week as the first argument:

SELECT date_part('week', TIMESTAMP '2017-09-30');

 date_part
-----------
        39
(1 row)

To get the current millennium, you use the DATE_PART() function with the NOW() function as follows:

SELECT date_part('millennium', now());

 date_part
-----------
         3
(1 row)

To extract the day part from a time stamp, you pass the day value to the DATE_PART() function:

SELECT date_part('day', TIMESTAMP '2017-03-18 10:20:30');

 date_part
-----------
        18
(1 row)

To extract the hour, minute, second from a time stamp, you pass the corresponding value hour, minute and second to the DATE_PART() function:

SELECT date_part('hour', TIMESTAMP '2017-03-18 10:20:30') h,
       date_part('minute', TIMESTAMP '2017-03-18 10:20:30') m,
       date_part('second', TIMESTAMP '2017-03-18 10:20:30') s;

 h  | m  | s
----+----+----
 10 | 20 | 30
(1 row)

To extract the day of week and or day of year from a time stamp, you use the following statement:

SELECT date_part('dow', TIMESTAMP '2017-03-18 10:20:30') dow,
       date_part('doy', TIMESTAMP '2017-03-18 10:20:30') doy;

 dow | doy
-----+-----
   6 |  77
(1 row)

In this tutorial, you have learned how to use the PostgreSQL DATE_PART() function to extract a subfield of a time stamp.

See more

PostgreSQL Tutorial: Date Functions

PostgreSQL Documentation: Date/Time Functions and Operators