PostgreSQL 17.5
DATE_PART(field, source)Parameters
- fieldtext
- Name of the part as a string
- Values:'century''day''decade''dow''doy''epoch''hour''isodow''isoyear''julian''microseconds''millennium''milliseconds''minute''month''quarter''second''timezone''timezone_hour''timezone_minute''week''year'
- sourcetimestamp | interval
- Date, time or interval
Return value
A floating-point number (double precision), for example 2023 for year
Examples
Year from a date
PostgreSQL 17.5
SELECT DATE_PART('year', TIMESTAMP '2023-01-01');Sunday as weekday 0
PostgreSQL 17.5
SELECT DATE_PART('dow', DATE '2024-03-10');Payments count by quarter
PostgreSQL 17.5
SELECT DATE_PART('quarter', date) AS quarter,
COUNT(*) AS payments
FROM Payments
GROUP BY quarter
ORDER BY quarter;Details
Unlike EXTRACT, which returns numeric, DATE_PART can lose precision.
Day of the week: 'dow' gives 0 for Sunday and 6 for Saturday, while 'isodow' goes from 1 for Monday to 7 for Sunday. MySQL DAYOFWEEK counts differently: Sunday is 1, Saturday is 7.
'week' is the ISO week number: a week starts on Monday, and the first days of January can belong to the last week of the previous year. That is why the week number is used together with 'isoyear', not 'year': for 2021-01-01 the week is 53 and the ISO year is 2020.