mysql - 周查询 : Mysql

Question

我想获取周一到周日开始的数据在条件选择第 2 周 1 月 11 日的条件下放置以下查询，但提供 1 月 10 日至 1 月 16 日的数据。

date_format(DATE_ADD(a.timestamp, INTERVAL(1-DAYOFWEEK(a.timestamp))+1 day),
'%b-%d, Week %U') = 'Jan-11, Week 02'

Answer 1

试试这个:

date_format(a.timestamp, '%u') = '02';

如果您需要将星期一作为一周的第一天，请使用“%u”而不是“%U”。

引用: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

%U  Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u  Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V  Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v  Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x

更新:如果输入值必须像一周的第一天'Jan-11, Week 02'，我们必须根据您的原始条款进行修改，仍然使用'%U'。看起来不太优雅，但应该可以。

date_format(DATE_ADD(a.timestamp, INTERVAL(1-DAYOFWEEK( DATE_SUB(a.timestamp, INTERVAL 1 DAY) )) day),'%b-%d, Week %U') = 'Jan-11, Week 02'