CODEWITHSUNDEEP
phpmysqlsqldatabase
Justin
Justin

Reputation: 653

How to make MySQL's WEEK() and YEARWEEK() function?

I still maintain an application that I built years and years ago which has a calendar type overview of data, organized by week.

In my code, there are references to the YEARWEEK() and WEEK() functions in MySQL.

For some reason, I'll get an email near the beginning of every year stating that the calendar links are not working correctly. (The calendar links are generated based on the week number of that year.)

In a nut shell, I end up passing in the mode parameter to the WEEK() and YEARWEEK() functions. So one year I'll pass in 1 as the second parameter, and the next year, I'll pass in 0. And basically, every year I'm forced to make this change programmatically to fix the problem, going back between 1 and 0.

I'm forced to do this because all the weeks will be offset by 1 when the calendar is not functioning correctly. So I might want to click on week 10, but I'm getting data for week 11.

Unless someone has specifically come across this problem, I don't know if it will be that easy for someone to come up with a good answer for me.

Upvotes: 1

Views: 1128

Answers (1)

Kad
Kad

Reputation: 658

Since you're referring to mode 0 and 1, I suspect you might not be aware there are actually eight modes. Without knowing the business rules for your website I can't tell which one you need, but the MySQL documentation is pretty explicit.

You must ask yourself three questions about your business rules:

  • Are weeks starting on a Sunday or Monday?
    • Modes (0,2,4,6) are for weeks starting on a Sunday
    • Modes (1,3,5,7) are for weeks starting on a Monday
  • Are there 53 or 54 weeks identifiers in a year?
    • Modes (0,1,4,5) are for 54 weeks year format
    • Modes (2,3,6,7) are for 53 weeks year format
  • Is the first week based on the first Sunday/Monday or the one with most weekdays in year?
    • Modes (0,2,5,7) is for the year containing the first day of the week
    • Modes (1,3,4,6) is for the year containing more than half of weekdays of the week

N.B. Although not documented for YEARWEEK(), the WEEK() modes are the same

Hope this helps!

Upvotes: 1

Related Questions