PostgreSQL DATE_PART Function: Extract Year, Month, Week, etc., From a Timestamp

Introduction to the PostgreSQL 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.

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

DATE_PART(field,source)
Code language: SQL (Structured Query Language) (sql)

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
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

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
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

To extract the quarter, you use the following statement:

SELECT date_part('quarter',TIMESTAMP '2017-01-01');
 date_part
-----------
(1 row)  
Code language: SQL (Structured Query Language) (sql)

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

 SELECT date_part('month',TIMESTAMP '2017-09-30');
 date_part
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

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

 SELECT date_part('decade',TIMESTAMP '2017-09-30');
 date_part
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

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
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

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

 SELECT date_part('millennium',now());
 date_part
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

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
-----------
(1 row)
Code language: SQL (Structured Query Language) (sql)

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)
Code language: SQL (Structured Query Language) (sql)

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;