从星期六开始的一周中的几周 [英] Week of the year for weeks starting with Saturday

问题描述

我们有客户目前在星期六，星期一或星期一开始定义周。来自这些DATE_FORMAT选项，很好地处理从星期天和星期一开始的一周，但是星期六从星期六开始，一周内没有办法。有什么建议么？

 ％U周（00..53），星期日是星期几
％u周（00..53），星期一是一周的第一天
  

解决方案

我有一个类似的问题：我需要根据以下规则计算周数：

• 周五从星期五开始


• 一年的剩余日子（一年中最后一个星期五之后的所有日子）应该计算在下一年的
  第一周。

例如：

• 27/12/2012（星期四）应为2012年第52周


• 28/12/2012（星期五）应为第1周2013年第一周2013年12月28日至2012年3月31日


• 
  
  

  我发表了这个声明，根据这些规则计算YEAR和WEEKNUMBER，您可以轻松地适应您的环境：

    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））; 
    

  棘手部分只是这个表达式：

    ceil（（dayofyear（current_date）+ dayofweek（date_format（current_date，'％Y-01-01'））+ 1）/ 7）
    

  其余的（If子句）仅适用于在第53周使表达式的结果使年+ 1和周= 1。 p>
  
  

  我尽量尽可能地解释表达式。以下表达式使您的星期数纯粹简单（一年中的日期除以每周7天的四舍五入）：

    ceil（（dayofyear（current_date））/ 7）
    

  但现在你想让它开始星期五（或任何其他日子）。为了做到这一点，你需要添加到当天，第一周的那一天，这是上一年的一部分（就像你目前实际上在几天前开始的，因为你的第一个星期包含了上一年的日子）。
  此表达式计算基于1月/ 1日的工作日的偏移量：

    dayofweek（date_format（current_date，'％ Y-01-01'））+ OFFSET 
    

  偏移量是7和周日数之间的差值想要星期开始：

  
  
  
  
  
  • 0星期六
  
  
  • 1星期五
  
  
  • 2星期四
  
  
  • 3星期三
    ...
  
  
  
  
  

  所以现在你只需要把它添加到上一个表达式中，就可以计算从任何一个工作日开始的周数，并假定第1周从上一年开始：

    ceil（（dayofyear（current_date）+ dayofweek（date_format（current_date，'％Y-01-01'））+ OFFSET）/ 7）
    

  然后，我刚刚添加了一个IF，将第53周变成第1周，另一个则在第53周添加1。

  
  

  We have customers that currently have defined weeks starting either on Sat, Sun or Monday. Came across these DATE_FORMAT options which nicely handle the week starting on Sunday and Monday but can't find a way to do same for 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
  

  解决方案

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

  • Week starts on Friday
  • The remainder days of an year (all the days after the last Friday of the year that do not complete a week) should be counted in the first week of the next year.

  For example:

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

  I made this statement that calculates both the YEAR and WEEKNUMBER based on these rules that you can easily adapt to your circunstance:

  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 tricky part is just this expression:

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

  The rest (If clauses) are just for adapting the result of the expression to make year+1 and week = 1 on week 53.

  I'll try to explain the expression as best as I can. The following expression gives you the week number pure simple (the day of the year divided by 7 days in a week rounded up):

  ceil(( dayofyear(current_date))/7)
  

  But now you want to make it start 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's like your current actually started a few days before, because your first week contains days from the previous year). This expression calculates that offset based on the weekday on Jan/1:

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

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

  • 0 for Saturday
  • 1 for Friday
  • 2 for Thursday
  • 3 for Wednesday ...

  So now you just have to add it to the previous one resulting in the above mentioned expression that calculates the week numbers starting on any weekday and assuming week 1 to start on the previous year:

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

  Then I just added an IF that turns week 53 into week 1 and another to add 1 to the year if it's week 53.

  这篇关于从星期六开始的一周中的几周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！