EXTRACT function
EXTRACT returns some time component from a time-based value, such as the year from a Timestamp.
Signatures
| Parameter | Type | Description |
|---|---|---|
| val | date, time, timestamp, timestamp with time zone, interval |
The value from which you want to extract a component. |
Arguments
EXTRACT supports multiple synonyms for most time periods.
| Time period | Synonyms |
|---|---|
| epoch | EPOCH |
| millennium | MIL, MILLENNIUM, MILLENNIA |
| century | C, CENT, CENTURY, CENTURIES |
| decade | DEC, DECS, DECADE, DECADES |
| year | Y, YEAR, YEARS, YR, YRS |
| quarter | QTR, QUARTER |
| month | MON, MONS, MONTH, MONTHS |
| week | W, WEEK, WEEKS |
| day | D, DAY, DAYS |
| hour | H, HR, HRS, HOUR, HOURS |
| minute | M, MIN, MINS, MINUTE, MINUTES |
| second | S, SEC, SECS, SECOND, SECONDS |
| microsecond | US, USEC, USECS, USECONDS, MICROSECOND, MICROSECONDS |
| millisecond | MS, MSEC, MSECS, MSECONDS, MILLISECOND, MILLISECONDS |
| day of week | DOW |
| ISO day of week | ISODOW |
| day of year | DOY |
Return value
EXTRACT returns a numeric value.
Examples
Extract second from timestamptz
SELECT EXTRACT(S FROM TIMESTAMP '2006-01-02 15:04:05.06');
extract
---------
5.06
Extract century from date
SELECT EXTRACT(CENTURIES FROM DATE '2006-01-02');
extract
---------
21