14

Can someone kindly explain me why these two give different results?

I execute this with PHP.

date("YW",mktime(0, 0, 0, 3, 22 , 2013)); // outputs 201312

And when I execute this with MySQL

SELECT YEARWEEK(now()); // outputs 201311
kittycat
  • 14,983
  • 9
  • 55
  • 80
Prasad Rajapaksha
  • 6,118
  • 10
  • 36
  • 52

3 Answers3

37

You need to specify mode 3 on the mysql YEARWEEK call:

SELECT YEARWEEK(now(),3);

The PHP date() placeholder W returns the week number according to the ISO 8601 specification. That means weeks start on Monday (not Sunday), the first week of the year is number 1 (not 0), and that week is the first one that with more than half its days in the new year (so it has to be January by Thursday). According to the documentation for the MySQL WEEK function, that combination of options is mode 3.

Also, to pull Alles's note into the accepted answer because it's important: the placeholders Y and W don't go together. If you want the year that goes with the ISO week number, you should use o instead of Y. For example, consider the week starting on Monday, December 29th, 2014:

date('YW', mktime(0,0,0,12,29,2014));  #=> 201401 : 1st week of 2014??
date('oW', mktime(0,0,0,12,29,2014));  #=> 201501 : better
Mark Reed
  • 91,912
  • 16
  • 138
  • 175
  • +1 for answer, but what if I want to use YEARWEEK to aggregate data weekly, and I want Sunday as first day of the week? PHP it seems don't have option for changing week starting day – dzona Feb 01 '14 at 16:51
  • That's what the second argument (the "mode") specifies. Read the documentation link... modes 0, 2, 4, and 6 all start the week on Sunday, differing in when week 1 is and whether there's a week 0 or 53. – Mark Reed Feb 01 '14 at 17:41
  • @MarkReed sorry, but you missed my question. I want PHP equivalent for MySQL mode 6 – dzona Feb 02 '14 at 23:53
  • Ah. There's no built-in support in PHP's `date` function for that. You'll have to do the math yourself. – Mark Reed Dec 01 '14 at 14:06
18

Please be aware that YEARWEEK('2012-01-01', 3) => 201152 while the PHP "YW" will give 201252. The year in the result may be different from the year in the date argument for the first and the last week of the year. (i.e. the year in the YEARWEEK is the year of the Monday of the week and not the year of the date being used to calculate).

In order to get the right result, you need to do

date("oW",mktime(0, 0, 0, 1, 1, 2012)); // outputs 201152

as "o" gives you the Year the week belongs to.

I hope this helps.

Alles
  • 181
  • 3
  • Thanks for this note - saved me a lot of confusion and potential hair-tearing today, since this today is part of next year's week. – cincodenada Dec 29 '14 at 19:24
3

YEARWEEK takes a second (optional) parameter that specifies the range of the week [0- 53] or [1-53]).

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used.

while date(W) is an ISO8601 date that is always in the range [01-53]. Therefore my guess is that by default YEARWEEK is using the [0-53] range.

So, if you want to get the same result try using 1 as the second parameter for YEARWEEK

Ares
  • 5,905
  • 3
  • 35
  • 51