PostgreSQL EXTRACT() Function

The PostgreSQL EXTRACT() function is used to extract parts from a date or datetime value.

Syntax

EXTRACT(unit FROM datetime)

Parameters

`datetime`	`Required.` Specify a date or datetime value from which the parts is to be extracted.
`unit`	`Required.` Specify the unit type of interval to extract. It can be one of the following values: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

Return Value

Returns the extracted part.

Example 1:

The example below shows the usage of EXTRACT() function.

SELECT EXTRACT(DAY FROM '2019-10-25');
Result: 25

SELECT EXTRACT(HOUR FROM '2019-10-25 08:10:25');
Result: 8

SELECT EXTRACT(MINUTE FROM '2019-10-25 08:10:25');
Result: 10

SELECT EXTRACT(SECOND FROM '2019-10-25 08:10:25');
Result: 25

SELECT EXTRACT(WEEK FROM '2019-10-25 08:10:25');
Result: 42

SELECT EXTRACT(MONTH FROM '2019-10-25');
Result: 10

SELECT EXTRACT(QUARTER FROM '2019-10-25');
Result: 4

SELECT EXTRACT(YEAR FROM '2019-10-25');
Result: 2019

SELECT EXTRACT(MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 123

SELECT EXTRACT(SECOND_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 25000123

SELECT EXTRACT(MINUTE_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 1025000123

SELECT EXTRACT(MINUTE_SECOND FROM '2019-10-25 08:10:25');
Result: 1025

SELECT EXTRACT(HOUR_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 81025000123

SELECT EXTRACT(HOUR_SECOND FROM '2019-10-25 08:10:25');
Result: 81025

SELECT EXTRACT(HOUR_MINUTE FROM '2019-10-25 08:10:25');
Result: 810

SELECT EXTRACT(DAY_MICROSECOND FROM '2019-10-25 08:10:25.000123');
Result: 25081025000123

SELECT EXTRACT(DAY_SECOND FROM '2019-10-25 08:10:25');
Result: 25081025

SELECT EXTRACT(DAY_MINUTE FROM '2019-10-25 08:10:25');
Result: 250810

SELECT EXTRACT(DAY_HOUR FROM '2019-10-25 08:10:25');
Result: 2508

SELECT EXTRACT(YEAR_MONTH FROM '2019-10-25 08:10:25');
Result: 201910

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpID	Name	LoginStamp
1	John	2019-10-25 09:20:38
2	Marry	2019-10-25 09:21:05
3	Jo	2019-10-25 09:24:35
4	Kim	2019-10-25 09:25:24
5	Ramesh	2019-10-25 09:27:16
6	Suresh	2019-10-25 09:28:19

To extract the HOUR_MINUTE part from the records of LoginStamp column, the following query can be used:

SELECT *, EXTRACT(HOUR_MINUTE FROM LoginStamp) AS EXTRACT_Value FROM EmployeeLogin;

This will produce a result similar to:

EmpID	Name	LoginStamp	EXTRACT_Value
1	John	2019-10-25 09:20:38	920
2	Marry	2019-10-25 09:21:05	921
3	Jo	2019-10-25 09:24:35	924
4	Kim	2019-10-25 09:25:24	925
5	Ramesh	2019-10-25 09:27:16	927
6	Suresh	2019-10-25 09:28:19	928

❮ PostgreSQL Functions