CODEWITHSUNDEEP
mysqlsqldate
John
John

Reputation: 173

MySQL yearweek() vs week() returning different result

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?

Upvotes: 6

Views: 2313

Answers (2)

Vishnu Vijaykumar
Vishnu Vijaykumar

Reputation: 450

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 )

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

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.

Upvotes: 2

Related Questions