Materialize Documentation
s
Join the Community github/materialize

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

date_part ( ' epoch millennium century decade year quarter month week dat hour minute second microsecond millisecond dow isodow doy ' , val )
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