SQL, data modeling, data quality, EDA, visualization · insight-1057
Pair the ISO week number with the ISO week-year, never with the calendar year
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A weekly report groups by EXTRACT(year ...) and EXTRACT(week ...), and the last days of December show up as week 1 next to January's data.
Action
Use the ISO week-year (isoyear) together with the ISO week, or build a calendar table with an explicit week_start_date and group by that date. State the week-start convention in the metric definition.
Reason
ISO 8601 weeks start on Monday and week 1 contains January 4, so week-numbering years do not match calendar years.
Boundary
Weekly KPIs, year-over-year comparisons by week and seasonality features.
Symptom or risk
ISO weeks can start in one calendar year and end in the next, so a calendar-year key merges Monday 30 December into "week 1 of this year" and week 52 and 53 comparisons mix.
Exceptions
Business calendars with their own fiscal weeks, which need their own calendar table.
Trade-offs
Week-start dates are more readable than week numbers but sort across years only if ISO.
Pitfalls
Comparing "week 1" across years without checking that both contain the same number of days of the year.
Example
From the PostgreSQL manual: 2012-12-31 falls in ISO week 1 of 2013, and 2006-01-01 is in ISO year 2005.
Lessons and concepts
Claims and their sources
In PostgreSQL, ISO weeks start on Monday, week 1 contains January 4, and isoyear can differ from the calendar year (for example 2012-12-31 is in week 1 of 2013).