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