如何获得“开始日期"?和“结束日期"星期数可用时，从MYSQL获得一周的时间? [英] How to get "start date" and "end date" of a week from MYSQL when week number is available?

问题描述

MySql的week()函数给出从0到53的星期数.

The week() function of MySql gives number of week from 0-53.

我有一个周号，我想用周号表示一周的"开始日期"和"结束日期". 我该怎么做?

I have a weeknumber and I want to have the "start date" and "end date" of the week represented by weeknumber. How can I do that?

如果无法直接获取一周的开始日期"和结束日期"，如何从周号中获取开始星期几"和结束星期几"?我尝试使用以下查询获取一周的第一天(星期一):-

If there is no way of getting "start date" and "end date" of a week directly, how can I the "start week day" and "end week day" from the week number? I tried to get the first day (Monday) of the week by using the following query:-

$currentWeek = 7;
for($w = 0; $w <= $currentWeek; $w++)
{
    $actualWeek = intval($w + 1);
    if($w < 10)
        $queryWeek = '0'.$actualWeek;
    else
        $queryWeek = $actualWeek;
    $thisYearWeek = date('Y').$queryWeek;
    $weekMondayQuery = $this->CustomerPayment->query("SELECT STR_TO_DATE('$thisYearWeek', '%X%V %W')");
}

2018年1月1日是星期一.对于第一周，即. $thisYearWeek = '201801'时，我将星期一日期设为 2018-01-08 ，而不是 2018-01-01 .

The Jan 1, 2018 was Monday. For the First week, ie. when $thisYearWeek = '201801', I am getting Monday date as 2018-01-08 instead of 2018-01-01.

推荐答案

如何在一年中的第一个星期一增加几天?

How about adding days to the first Monday in a year?

假设2018-01-01是第一周的星期一，而您需要的周数是$num_week:

Assuming 2018-01-01 is a Monday in a first week and the number of week you need is $num_week:

SELECT '2018-01-01' + INTERVAL ($num_week-1)*7 DAY as start, 
       '2018-01-01' + INTERVAL $num_week*7-1 DAY as end;

第7周提供了以下信息:

Which for the 7th week gives:

>>> SELECT '2018-01-01' + INTERVAL 42 DAY as start, 
           '2018-01-01' + INTERVAL 48 DAY as end;

start       end
2018-02-12  2018-02-18

如果一年的第一天不是星期一，则添加更正.但是您的实现方式取决于您如何使用week()函数计算周数:

Added correction if the first day of the year is not a Monday. But your implementation will depend on how you count weeks with a week() function:

>>> select '2016-01-01'+interval ($num_week-week('2016-01-01', 1))*7 - weekday('2016-01-01') day as start, 
           '2016-01-01'+interval ($num_week-week('2016-01-01', 1)+1)*7-1 - weekday('2016-01-01') day as end;

start       end
2016-02-15  2016-02-21

>>> select '2017-01-01'+interval ($num_week-week('2017-01-01', 1))*7 - weekday('2017-01-01') day as start, 
           '2017-01-01'+interval ($num_week-week('2017-01-01', 1)+1)*7-1 - weekday('2017-01-01') day as end;

start       end
2017-02-13  2017-02-19

这篇关于如何获得“开始日期"?和“结束日期"星期数可用时，从MYSQL获得一周的时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！