Week of the year starting from Saturday

Question

We have clients who have currently identified weeks starting from Saturday, Sunday, or Monday. We found through these parameters DATE_FORMAT, which perfectly handle the week starting on Sunday and Monday, but cannot find a way to do the same for the week starting on Saturday. Any suggestions?

%U Week (00..53), where Sunday is the first day of the week %u Week (00..53), where Monday is the first day of the week 

Answer 1

I had a similar problem: I needed to calculate the week numbers based on the following rules:

• Week Starts Friday
• The remaining days of the year (all days after the last Friday of the year that do not end in the week) should be considered in the first week of the next year.

For example:

• 12/27/2012 (Thursday) should be week 52 of 2012
• 12/28/2012 (Friday) should be 1 week of 2013.
• Week 1 of 2013 is from 12/28/2012 to 3/1/2013

I made this expression that calculates both YEAR and WEEKNUMBER based on these rules, which you can easily adapt to your event:

 SELECT IF(ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7) > 52, YEAR(current_date)+1, YEAR(current_date)), IF(ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7) > 52, 1, ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7)); 

The hard part is just an expression:

 ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+1 )/7) 

The rest (If clauses) are intended only to adapt the result of the expression to make year + 1 and week = 1 at week 53.

I will try to explain this expression as best as possible. The following expression gives you the week number purely simple (the day of the year divided by 7 days a week is rounded up):

 ceil(( dayofyear(current_date))/7) 

But now you want to start it on Friday (or any other day). To do this, you need to add to the current day, the days of the first week that were part of the previous year (it looks like your current one really started a few days ago, because your first week contains days from the previous year). This expression calculates this offset depending on the day of the week in January / 1:

 dayofweek(date_format(current_date, '%Y-01-01'))+OFFSET 

The offset is the difference between 7 and the weekday number you want to start the week:

• 0 for saturday
• 1 on friday
• 2 for thursday
• 3 for Wednesday ...

So, now you just need to add it to the previous one, resulting in the above expression, which calculates the numbers of the weeks starting on any day of the week and assuming that week 1 starts in the previous year:

 ceil(( dayofyear(current_date) + dayofweek(date_format(current_date, '%Y-01-01'))+OFFSET )/7) 

Then I just added IF, which turns week 53 into week 1, and the other - add 1 to the year if in week 53.

Answer 2

It took me a while to think about this issue.

The ISO standard defines the first week, which begins on Monday and will contain the 4th day of the year. MySQL functions provide many more options .

date_format() flags %U and %U use the notation where the first week is where Sunday or Monday first occurs. Since this is not ISO compliant, I will provide both options.

If you want to count week numbers starting from Saturday and the first week, this is the one that contains Saturday, you can use one of the following expressions:

 SELECT sign(dayofweek(current_date) - 7) + ceil(dayofyear(current_date)/7); SELECT ceil((dayofyear(current_date)+ (dayofweek(date_format(current_date, '%Y-01-01'))%7-7))/7); 

If the first annual week refers to the fourth year of the year, use:

 SELECT ceil((dayofyear(current_date)+ (dayofweek(date_format(current_date, '%Y-01-04'))%7-4+1))/7); 

The very first expression is pretty simple.

I will talk about the 2nd and 3rd. I calculate the week number, taking the current day of the year, dividing by 7 and the ceiling up, is quite simple. However, the number of weeks should be adjusted depending on the situation at the beginning of the year.

• for the first case (the first week starts on the first Saturday), I take the day of the week for January / 1 year in the subject, make Saturday as day 0 , and then adjust the day-year difference. It does all the days until the first Saturday, giving a negative number of adjustments, and it comes to zero;
• for the second case (the first week is the one where 4 days of the year fall), I take the day of the week for January / 4 years in the subject, making Saturday day 0. The formula -4+1 gives the correction on the first Saturday until January / 4, +1 used, since the days of the year begin with 1, and not 0. A negative setting means that the 1st day of the year is not in the first week of the year.

Here are some test dates for SQL Fiddle .

If you want to count weeks from any other day, you just need to change the formula by making this day 0 in sequence. Say, to count weeks starting on Wednesday, use:

 SELECT ceil((dayofyear(current_date)+ ((dayofweek(date_format(current_date, '%Y-01-04'))+3)%7-4+1))/7); 

+3 used because it complements the dayofweek() value for the environment to 7.

Answer 3

Make the setting based on DAYOFWEEK() .