date_part function
date_part returns some time component from a time-based value, such as the year from a Timestamp.
It is mostly functionally equivalent to the function EXTRACT, except to maintain
PostgreSQL compatibility, date_part returns values of type float. This can
result in a loss of precision in certain uses. Using EXTRACT is recommended instead.
Signatures
| Parameter | Type | Description |
|---|---|---|
| val | time, timestamp, timestamp with time zone, interval, date |
The value from which you want to extract a component. vals of type date are first cast to type timestamp. |
Arguments
date_part 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
date_part returns a float value.
Examples
Extract second from timestamptz
SELECT date_part('S', TIMESTAMP '2006-01-02 15:04:05.06');
date_part
-----------
5.06
Extract century from date
SELECT date_part('CENTURIES', DATE '2006-01-02');
date_part
-----------
21