mysql每周起止日期_如何获得“开始日期”？和“结束日期”星期数可用时，从MYSQL获得一周？...

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')");

}

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?

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;

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

EDIT: 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