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. 2024), 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.

Read more

Learn more about week numbers and the ISO week numbering scheme in this little primer.