PostgreSQL DATE_PART Function

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 ----------- 21 (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 ----------- 2017 (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 (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 ----------- 9 (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 ----------- 201 (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 ----------- 39 (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 ----------- 3 (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 ----------- 18 (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; dow | doy -----+----- 6 | 77 (1 row)
Code language: SQL (Structured Query Language) (sql)

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

Was this tutorial helpful ?