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:

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:

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

To extract the quarter, you use the following statement:

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

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

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

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

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

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

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

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