EXTRACT function to retrieve subfields from date/time values - PolarDB

EXTRACT retrieves a subfield such as year or hour from a date/time value.

Syntax

EXTRACT(field FROM source)

Argument	Description
`field`	The subfield to extract. See Supported subfields for valid values.
`source`	A date/time value, typically a `TIMESTAMP` expression.

DOUBLE PRECISION

Subfield	Description	Range
`YEAR`	The year	—
`MONTH`	The month within the year	1–12
`DAY`	The day of the month	1–31
`HOUR`	The hour of the day	0–23
`MINUTE`	The minute of the hour	0–59
`SECOND`	The second of the minute, including the fractional part	0–59

All examples use the same input timestamp: TIMESTAMP '2001-02-16 20:38:40'.

Extract the year:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
      2001
(1 row)

Extract the month:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
         2
(1 row)

Extract the day:

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
        16
(1 row)

Extract the hour:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
        20
(1 row)

Extract the minute:

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
        38
(1 row)

Extract the second:

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
        40
(1 row)