PostgreSQL extract function.

Preamble

PostgreSQL extract function extracts parts from the date.

Syntax of the extract function in PostgreSQL

extract( unit from date )

Parameters and function arguments

• date – date, timestamp, time, or interval value from which a part of the date is to be extracted.
• Unit – Type of the unit of interval measurement, such as day, month, minute, hour, etc. This may be one of the following:

The extract function can be used in future versions of PostgreSQL

Let’s look at some examples of extract functions to see how to use the extract function in PostgreSQL with date values
For example:

SELECT extract(day from date '2019-04-23');

--Result: 25

SELECT extract(month from date '2019-04-23');

--Result: 4

SELECT extract(year from date '2019-04-23');

--Result: 2019

Let’s see how to use the extract function in PostgreSQL with timestamp values.
For example:

SELECT extract(day from timestamp '2019-04-23 08:44:21');

--Result: 23

SELECT extract(month from timestamp '2019-04-23 08:44:21');

--Result: 4

SELECT extract(minute from timestamp '2019-04-23 08:44:21');

--Result: 44

SELECT extract(hour from timestamp '2019-04-23 08:44:21');

--Result: 8

Let’s see how to use the extract function in PostgreSQL with time values.
For example:

SELECT extract(minute from time '08:44:21');

--Result: 44

SELECT extract(milliseconds from time '08:44:21.7');

--Result: 21700

Let’s see how to use the extract function in PostgreSQL with interval values.
For example:

SELECT extract(day from interval '5 days 3 hours');

--Result: 5

SELECT extract(hour from interval '5 days 3 hours');

--Result: 3

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.