Week numbers in PostgreSQL
To get the current ISO 8601 week number in PostgreSQL, use the following code snippet:
SELECT EXTRACT(WEEK FROM CURRENT_DATE);
* Ensure your system or application regional settings are set to follow ISO 8601 standards (weeks start on Monday) if the output varies.
Step-by-Step Instructions
- Run: SELECT EXTRACT(WEEK FROM CURRENT_DATE);
- PostgreSQL's WEEK field is ISO 8601 by definition, no mode argument needed.
- For the matching ISO year use EXTRACT(ISOYEAR FROM CURRENT_DATE).
Good to Know
Pair WEEK with ISOYEAR rather than YEAR in GROUP BY clauses: on dates like January 1 the calendar year and the ISO year differ, and grouping by the wrong one splits one ISO week across two buckets.