6

This simple query does not seem to be outputting the correct results. Given that the mode is the same (1) for all the functions shouldn't "week("2018-12-31", 1)" show a value of 1 rather than 53? 

SELECT yearweek("2018-12-31", 1), yearweek("2019-01-02", 1),
week("2018-12-31", 1), week("2019-01-02", 1)

which outputs the following.

'201901','201901','53','1'

Is there a default dev more that I need to set somewhere?

Salman A
  • 262,204
  • 82
  • 430
  • 521
John
  • 173
  • 1
  • 1
  • 6

2 Answers2

2

Have a look at this example from WEEK function description:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0

One might argue that WEEK() should return 52 because the given date actually occurs in the 52nd week of 1999. WEEK() returns 0 instead so that the return value is “the week number in the given year.”

The above seem to suggest that all modes having 0-53 range would return the week number relative to the year of input date (it'll return 0 if the date falls in the last week of the previous year).

So if first day of year = Monday and first week of year = having 4 or more days this year, then 2018-12-31 belongs to 53rd week of 2018 -and- 1st week of 2019, and the mode parameter determines the return value:

SELECT WEEK('2018-12-31', 1); -- 53
SELECT WEEK('2018-12-31', 3); -- 1

The YEARWEEK function is unambiguous (the result includes the year) so above does not apply.

Salman A
  • 262,204
  • 82
  • 430
  • 521
0

week() in mode 1: First day of week is Monday and the first week of the year has more than 3 days.

So 2018 started with week 1 and not from week 0 (because the week with 1st Jan 2018 had more than three days of 2018). Hence 31-12-2018 falls under week 53.

Similarly, 2019 starts with week 1 for 01-01-2019 until 06-01-2019 and week 2 from 07-01-2019 to 13-01-2019 (since Monday is first day of the week and the week containing Jan 1st has 6 days of 2019 )

Vishnu Vijaykumar
  • 412
  • 4
  • 14
  • 1
    So why does week("2019-01-02", 1) return 1? And how does this comply to the [MySQL documentation](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week)? – Christoph Sonntag Jan 03 '19 at 12:49
  • @Compufreak all modes having 0-53 range would return the week number relative to the year of input date. https://stackoverflow.com/a/54021772/9168603 refer this answer for details – Vishnu Vijaykumar Jan 03 '19 at 12:51
  • I am referring to the last part of your answer - you say 2019 starts with week 0, so why doesn't `week("2019-01-02", 1)` return 0? You also say `week("2019-01-07", 1)` is week 1, but it does return 2. – Christoph Sonntag Jan 03 '19 at 12:55
  • @Compufreak Oh I am so sorry, I might have mistyped. It is indeed week 1 because it contains 5 days of 2019. I will edit the answer – Vishnu Vijaykumar Jan 03 '19 at 12:57
  • 1
    Btw, the week containing Jan 1st has *six* days in 2019 – dnoeth Jan 03 '19 at 13:11