记录MySQL处理日期

先有如下需求：每周以周五为周的开始，持续到下周四为止；要输出给定日期的年份，周数，开始时间，结束时间

输入示例：
2019-12-27
输出示例：
2020,1,2019-12-27,2020-01-022019-12-27

输入示例：
2020-02-27
输出示例：
2020,9,2020-02-21,2020-02-27

 

select 
       year(
               if(5 - if(dayofweek(str_to_date(${strDate}, '%Y-%m-%d')) = 1, 8,
                         dayofweek(str_to_date(${strDate}, '%Y-%m-%d'))) < 0,
                  date_format(date_add(subdate(${strDate},
                                               if(date_format(${strDate}, '%w') = 0, 7, date_format(${strDate}, '%w')) -
                                               1),
                                       interval 5 - 1 + 6 day), '%Y-%m-%d'),
                  date_format(date_add(subdate(date_sub(${strDate}, interval 7 day),
                                               if(date_format(date_sub(${strDate}, interval 7 day), '%w') = 0, 7,
                                                  date_format(date_sub(${strDate}, interval 7 day), '%w')) - 1),
                                       interval 5 - 1 + 6 day), '%Y-%m-%d')
                   )
           )                         as YEAR,
       if(5 - if(dayofweek(date_add(date(${strDate}), interval 7 day)) = 1, 8,
                 dayofweek(date_add(date(${strDate}), interval 7 day))) < 0,
          week(date_add(date(${strDate}), interval 7 day), 3),
          week(date(${strDate}), 3)) as WEEKS,
       if(5 - if(dayofweek(date(${strDate})) = 1, 8, dayofweek(date(${strDate}))) < 0,
          date_format(date_add(subdate(${strDate},
                                       if(date_format(${strDate}, '%w') = 0, 7, date_format(${strDate}, '%w')) - 1),
                               interval 5 - 1 day), '%Y-%m-%d'),
          date_format(date_add(subdate(date_sub(${strDate}, interval 7 day),
                                       if(date_format(date_sub(${strDate}, interval 7 day), '%w') = 0, 7,
                                          date_format(date_sub(${strDate}, interval 7 day), '%w')) - 1), interval 5 - 1
                               day), '%Y-%m-%d')
           )                         as START_DAY,
       if(5 - if(dayofweek(str_to_date(${strDate}, '%Y-%m-%d')) = 1, 8,
                 dayofweek(str_to_date(${strDate}, '%Y-%m-%d'))) < 0,
          date_format(date_add(subdate(${strDate},
                                       if(date_format(${strDate}, '%w') = 0, 7, date_format(${strDate}, '%w')) - 1),
                               interval 5 - 1 + 6 day), '%Y-%m-%d'),
          date_format(date_add(subdate(date_sub(${strDate}, interval 7 day),
                                       if(date_format(date_sub(${strDate}, interval 7 day), '%w') = 0, 7,
                                          date_format(date_sub(${strDate}, interval 7 day), '%w')) - 1),
                               interval 5 - 1 + 6 day), '%Y-%m-%d')
           )                         as END_DAY;

如需有详细需求（例如：以周四为周起始可以将SQL中的数字5改为4）