Week numbers in PostgreSQL
How to get the week number from a date
To get the ISO week number (1-53) from a date in the column datecol, use
SELECT EXTRACT(WEEK FROM datecol) FROM ….
To get the corresponding four-digit year, use
SELECT EXTRACT(ISOYEAR FROM datecol) FROM ….
Read more about EXTRACT() in the PostgreSQL manual.
To get the week number in a TO_CHAR pattern, use
IW for the week number and
IYYY for the corresponding year.
Read more about TO_CHAR() in the PostgreSQL manual.
How to get the date from a week number
To get the date of Monday in a given week, use
SELECT TO_DATE(CONCAT(yearcol, weekcol), 'IYYYIW') FROM ….
yearcol is a 4-digit year (e.g. 2023), and weekcol is an ISO week number (1-53).
Read more about TO_DATE() in the PostgreSQL manual.
How to get the number of weeks in a year
To get the number of ISO weeks (i.e. the number of the last week) in a year, get the week number of 28 December in that year using the above logic, i.e.
SELECT EXTRACT(WEEK FROM MAKE_DATE(yearcol, 12, 28)) FROM ….
This is based on the fact that the last week of the year always includes 28 December.