Using the ‘week’ as a time unit can be a bit confusing for at least two reasons:
- Does the week start on Sunday or Monday?
- How do you count a week that stretches two years?
Now probably your and anybody else’s take on those two questions will not just depend on cultural upbringing – but even more on – how it has been done so far. Nobody wants to change old statistics to a new counting or maintain two parallel statistics for one year with both countings. So to make a long story short, I’ll just stick with ISO 8601 standard because it seems reasonable to me. Here is what ISO 8601 has to say about the two questions:
- Monday is the first day of a week.
- The first answer implies that Thursday is the central day of a week. The year thursday lies in is also the one which the week then belongs to (makes sense).
In my not that long career as a Data Analyst I already encountered quite a number of different week numberings – most of them didn’t make any sense and just led to wrong aggregation of the respective figures for the ambiguous last/first week. So it’s good to just get that straight once and then stick with it.
Usually my data comes from MySQL databases. So these are the two statements I use for the year of the week and the one for the week (if you know an easier statement for the year of the week – please let me know!):
@d := '2014-12-29',
# year (1)
date_format(@d1, "%x") as yw1;
# year (2)
convert(substring(yearweek(@d,3),1,4), unsigned) as yw2,
# year (3)
# calculates the year of the week
if(week(@d,1) = 0,
if(week(@d,3) = 1 and month(@d) = 12,
) as yw3,
# week (1)
week(@d,3) as w1,
# week (2)
date_format(@d, "%v") as w2
PHP applies ISO 8601 for the date time format codes by default:
|o||the week’s year (determined by a day from the week)|
|N||the weekday numbering starting with Monday|
Stick with ISO and you won’t have funny numbers in your new year’s reporting – and while you’re at it, also accept the metric system if you didn’t yet ;).