DATE_PART() is a built-in function in PostgreSQL that is used to extract/retrieve a specific part(like a month, year, hour, minutes, etc.) from a date or time. It takes two parameters, a “field” and a “source”. The field determines which date/time part will be extracted/pulled out from the given source.

In this write-up, we are going to learn the functionality of the DATE_PART() function with the help of some examples. So, let’s start!

How to Use DATE_PART() Function in PostgreSQL?

The DATE_PART() function allows us to extract the desired subfield(such as year, month, seconds, minutes, etc.) from the given date or time value. The syntax of the DATE_PART() will be as follows:

DATE_PART(field, source);

Here, the source represents a Time, or Interval while the field represents a date/time part to be extracted from the source. The field’s value must be one of the following:

Day, Month, Year, Decade, Quarter, Century, Hour, Minute, Second, Microsecond, Millisecond, DOW(Day of Week), DOY(Day of Year), Epoch, ISOYear, ISODow, timezone_minute, timezone_hour, or time zone.

Example #1: How to Use DATE_PART() to Extract a Day From a TIMESTAMP?

In this example, we have a timestamp “2022-08-16”, and the task is to extract the day from the given timestamp:

SELECT DATE_PART('DAY', TIMESTAMP '2022-08-16');

The output shows that the DATE_PART() function successfully extracts the day part from the given TIMESTAMP. The month and year can also be extracted from the given TIMESTAMP by specifying the "MONTH" and "YEAR" as field values, respectively.

Example #2: How to Extract a Century From a TIMESTAMP?

Let’s pass the “CENTURY” as the first argument to the DATE_PART() function to extract the century from the given TIMESTAMP:

SELECT DATE_PART('CENTURY', TIMESTAMP '2022-08-16');

The output proves that the DATE_PART() function successfully extracted the century from the given TIMESTAMP.

Example #3: How to Extract a Quarter From a TIMESTAMP?

Passing the “QUARTER” as a field value will extract the current quarter from the given TIMESTAMP:

SELECT DATE_PART('QUARTER', TIMESTAMP '2022-08-16');

There are four quarters in a year and one quarter is equal to three months. In the given TIMESTAMP, the current month is 8, which lies in the third quarter. So, the DATE_PART() function will return the following output:

The output verifies the working of the DATE_PART() function.

Example #4: How to Extract a Week From a TIMESTAMP?

Let’s pass the “WEEK” as a first argument to the DATE_PART() function to get the current week from the given TIMESTAMP:

SELECT DATE_PART('WEEK', TIMESTAMP '2022-08-16');

The output authenticates the working of the DATE_PART() function.

Example #5: How to Extract a ‘DAY OF WEEK’ From a TIMESTAMP?

Pass DOW as a first argument to the DATE_PART() function to extract the day of the week from the given TIMESTAMP:

SELECT DATE_PART('DOW', TIMESTAMP '2022-08-16');

The output authenticates that the DATE_PART() function produces an accurate result (it's the second day of the week i.e., Tuesday).

Example #6: How to Extract a Minutes From a TIMESTAMP?

To extract the minutes from the given TIMESTAMP, pass the “MINUTES” as the first argument to the DATE_PART() function:

SELECT DATE_PART('MINUTE', TIMESTAMP '2022-08-16 11:11:21');

The output shows that the DATE_PART() function provides accurate results.

Example #7: How to Use DATE_PART() Function on Table’s Data?

We created a table named bike_details in our database. Here are the details about the bike_details table:

SELECT * FROM bike_details;

Suppose we have to fetch only month from the launch date. We will execute the below query to get the MONTH from the 'bike_launch_date' column:

SELECT DATE_PART('MONTH', bike_launch_date) FROM bike_details;

The output proved that the DATE_PART() function succeeded in fetching the month part from the selected column. In this way, you can use the DATE_PART() function to fetch any part of the date/time from a table.

Conclusion

In PostgreSQL, DATE_PART() is a built-in function that extracts/retrieves a specific part(like a month, decade, hour, minutes, etc.) from a given TIMESTAMP. Pass a field(like a month, decade, hour, minutes, etc.) as a first argument and a TIMESTAMP as a second argument to the DATE_PART() function. The field determines which date/time part will be extracted from the specified source/timestamp. This write-up described the usage of the DATE_PART() function with the help of different examples.