PostgreSQL Extract Function

Introduction

There are instances that we only require a portion of a particular timestamp or a date interval thus we need to strip away some of the unnecessary values in there be it a simple payroll calculation or an advance programming calculation purposes. Fortunately PostgreSQL allows to do just that using the EXTRACT() function that allow us to pick and grab the specific data on a given timestamp. Let’s dive into learning about the PostgreSQL Extract function.

Prerequisite

  • Ensure that PostgreSQL is installed and properly configured on your system.

PostgreSQL EXTRACT() Syntax

The EXTRACT() function syntax is fairly simply as shown below:

1
EXTRACT(field FROM <timestamp>)

Or

1
EXTRACT(field FROM <interval>)

The EXTRACT() function requires (2) arguments, which is the field and the source which will return a value of double precision.

EXTRACT() Function Valid Field

  • The PostgreSQL EXTRACT() function requires valid field values the field argument determines the field to be extracted from the source value.

The below list are some of the valid field of EXTRACT() function.

1. YEAR

  • 1.1 Year as composed of four (4) digits.
  • 1.2 This will be the same as the time stamp (i.e 2019-12-18 11:24:45), In this case the YEAR will be 2019.

2. MONTH

  • 2.1 The number for the Month is 1-12 should it be used as a ‘date value’.
  • 2.2 The number for the Month is 0-11 should it be used as a ‘interval value’ or ‘modulo’.

3. WEEK

  • 3.1 This will be an ISO 8601 based numbering which means that every year is composed of 52 or 53 weeks which starts on a Monday that has 4th of January.

4. DAY

  • 4.1 This will be the number of day within a month which 1-31

5. HOUR

  • 5.1 The hour which is from 0 – 24.

6. MINUTE

  • 6.1 The Minute which is from 0 – 59.

7. MILLISECONDS

  • 7.1 Its a seconds field which is a fraction of second that is multiplied by one thousand (1,000).

8. MICROSECONDS

  • 8.1 Its a seconds field which is a fraction of second that is multiplied by one million (1,000,000)

9. DOY

  • 9.1 This is the day of the whole year that ranges from 0 to 366 meaning the first day of the year is 1 and the last day of the year is 365, which depends if its a leap year or not.

10. DOW

  • 10.1 This describes the day of the week which means that the first day is equivalent to zero (0) which is Sunday and ends on the same day with a number of (6). (i.e 0 = Sunday, 1 = Monday and so on.)

11. CENTURY

  • 11.1 This field uses the Gregorian calendar which means that it begins at 0001-01-01 00:00:00 AD

12. DECADE

  • 12.1 A decade is equivalent to ten years.

EXTRACT() example

In this section we will show you various ways on how to use the PostgreSQL EXTRACT() function.

NOTE: We will be using this timestamp for our demo purposes. 2019-12-18 11:24:45.

Extracting a MONTH from a Timestamp

This section will show you how to extract MONTH from the timestamp.

1
SELECT EXTRACT(MONTH FROM TIMESTAMP '2019-12-18 11:24:45');

The result is :

1
2
3
4
 date_part
-----------
        12
(1 row)

Extracting a WEEK from a Timestamp

This section will show you how to extract WEEK from the timestamp.

1
SELECT EXTRACT(WEEK FROM TIMESTAMP '2019-12-18 11:24:45');

The result is:

1
2
3
4
 date_part
-----------
        51
(1 row)

Extracting a DAY from a Timestamp

This section will show you how to extract DAY from the timestamp.

1
SELECT EXTRACT(DAY FROM TIMESTAMP '2019-12-18 11:24:45');

The result is:

1
2
3
4
 date_part
-----------
        18
(1 row)

Up to this point we have been using the basic fields with an obvious expected result, now let’s try the fields that with a more complex calculations.

Extracting the DAY OF YEAR (DOY) from a Timestamp

This section will show you how to extract the day of the year from the timestamp.

1
SELECT EXTRACT(DOY FROM TIMESTAMP '2019-12-18 11:24:45');

The result is:

1
2
3
4
 date_part
-----------
       352
(1 row)

Extracting the DAY OF WEEK (DOW) from a Timestamp

This section will show you how to extract the day of week from the timestamp.

1
SELECT EXTRACT(DOW FROM TIMESTAMP '2019-12-18 11:24:45');

The result is:

1
2
3
4
 date_part
-----------
         3
(1 row)

Conclusion

In this tutorial, we have showed you how to extract a desired field from a timestamp.

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.