The EXTRACT function retrieves subfields such as year or hour from date/time values. This function returns a value of the data type DOUBLE PRECISION.
YEAR
The year field.
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
2001
(1 row)
MONTH
The number of the month within the year (1-12).
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
2
(1 row)
DAY
The day of the month (1-31).
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
16
(1 row)
HOUR
The hour of the day (0-23).
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
20
(1 row)
MINUTE
The minute of the hour (0-59).
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
38
(1 row)
SECOND
The second of the minute, including the fractional part (0-59).
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
40
(1 row)