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!):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
select @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, year(@d)-1, if(week(@d,3) = 1 and month(@d) = 12, year(@d)+1, year(@d) ) ) 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:
| W | week number |
| 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 ;).