Using the DATE_PART Function in PostgreSQL

Introduction

When you’re using PostgreSQL to manage your data, there may be times when you want to return just part of a date and time value. In these cases, the DATE_PART() function can be used to return a subfield or interval of a date value. This article provides an overview of the DATE_PART() function in PostgreSQL and explains how to use the function to pull out a defined subfield from a date.

Prerequisite

Before proceeding with this tutorial, you’ll need to make sure that PostgreSQL is installed and configured on your system.

PostgreSQL DATE_PART() Syntax

The basic syntax for the DATE_PART() function looks like the following:

date_part('unit',source)

The DATE_PART() function takes two arguments: the unit and the source.

  • UNIT – This argument represents the time interval value that we want to extract.
  • SOURCE – This argument represents the date, time, or timestamp from which we want to extract the value.

Shown below are some of the units of time that we can use in place of interval:

  • CENTURY – This unit uses the Gregorian calendar which means that it begins at 0001-01-01 00:00:00 AD
  • DAY – This unit represents the day of the month, so its values will span from 1 to 31.
  • DECADE – This unit is composed of a 10-year interval.
  • HOUR – This unit represents the hour, so its values will span from 0 to 23.
  • MICROSECOND – This unit represents the seconds part of a date multiplied by one million.
  • MILLENNIUM – This unit represents the millennium value. For example, dates in the 2000s are in the third millennium.
  • MILLISECOND – This unit represents the seconds part of a date multiplied by one thousand.
  • MINUTE – The minute unit is composed of 60 seconds.
  • MONTH – This unit represents the month of the year, so its values will span from 1 to 12.
  • QUARTER – This unit is equivalent to four months` time.
  • SECOND – This unit represents the seconds value.
  • WEEK – This unit is an ISO 8601-based number, which means that every year is composed of 52 or 53 weeks. In the ISO numbering system, weeks begin on a Monday, and the first week of a year is the one that contains January 4th.
  • YEAR – This unit represents the year value and is composed of four digits.

PostgreSQL DATE_PART() Practical Examples

In this section, we’ll look at a series of examples that demonstrate how to use the DATE_PART() function.

NOTE: We will be using the following timestamp for our demo purposes: 2019-12-21 11:13:45.

DATE_PART() with HOUR

The following example shows how to use DATE_PART() with HOUR:

SELECT DATE_PART('hour', TIMESTAMP '2019-12-21 11:13:45');

The result will look like this:

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

DATE_PART() with MINUTE

Next, we’ll look at an example that uses DATE_PART() with MINUTE:

SELECT DATE_PART('minute', TIMESTAMP '2019-12-21 11:13:45');

The result should look like the following:

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

DATE_PART() with DAY

Our next example shows how to use DATE_PART() with DAY:

SELECT DATE_PART('day', TIMESTAMP '2019-12-21 11:13:45');

We should see a result that looks like the following:

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

DATE_PART() with WEEK

The final example in this section shows how to use the DATE_PART() function with WEEK:

SELECT DATE_PART('week', TIMESTAMP '2019-12-21 11:13:45');

The result is shown below:

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

Conclusion

When you want to retrieve just one part of a date and time value, it’s important to know how to obtain the information you need. In this tutorial, we discussed the DATE_PART() function in PostgreSQL and showed you some basic examples of how the function works. With these instructions and examples, you’ll be able to incorporate the DATE_PART() function into your own database queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.