Get the start date of a MySQL calendar week (weekyear)

Question

All date format in European (yyyy-mm-dd).

I am trying find the corresponding start date for a week reported by the weekofyear(this is, week starting on Monday).

set @date0 ='2012-12-31';
set @date1 ='2013-01-01';
select weekofyear(@date0), weekofyear(@date1);
> 1, 1

SQL Fidle 1 This is, both dates are in the first week of 2013. However, if I try to extract the year and weekofyear I will get different results (which means I need to find a different strategy):

set @date0 ='2012-12-31';
select year(@date0), weekofyear(@date0);
>2012,1
set @date1 ='2013-01-01';
select year(@date1), weekofyear(@date1);
>2013,1

SQl Fidle 2 If I manually consult the calender I can see to which year they belong (2013).

this big case will output the week start date

set @date1 ='2012-01-01';

select 
    case when weekofyear(@date1) <> weekofyear(date_sub(@date1 , interval 1 day)) then @date1
        when weekofyear(@date1) <> weekofyear(date_sub(@date1 , interval 2 day)) then date_sub(@date1 , interval 1 day)
        when weekofyear(@date1) <> weekofyear(date_sub(@date1 , interval 3 day)) then date_sub(@date1 , interval 2 day)
        when weekofyear(@date1) <> weekofyear(date_sub(@date1 , interval 4 day)) then date_sub(@date1 , interval 3 day)
        when weekofyear(@date1) <> weekofyear(date_sub(@date1 , interval 5 day)) then date_sub(@date1 , interval 4 day)
        when weekofyear(@date1) <> weekofyear(date_sub(@date1 , interval 6 day)) then date_sub(@date1 , interval 6 day)
        else date_sub(@date1 , interval 6 day) end as week_start_date;
>2011-12-26

SQL Fidle 3

and this big case will also generate the combo year-week

set @date1 ='2012-12-31';
select 
    case when weekofyear(@date1) <> weekofyear(date_add(@date1 , interval 1 day)) then concat(year(@date1),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1)) 
        when weekofyear(@date1) <> weekofyear(date_add(@date1 , interval 2 day)) then concat(year(date_add(@date1 , interval 1 day)),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1)) 
        when weekofyear(@date1) <> weekofyear(date_add(@date1 , interval 3 day)) then concat(year(date_add(@date1 , interval 2 day)),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1)) 
        when weekofyear(@date1) <> weekofyear(date_add(@date1 , interval 4 day)) then concat(year(date_add(@date1 , interval 3 day)),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1)) 
        when weekofyear(@date1) <> weekofyear(date_add(@date1 , interval 5 day)) then concat(year(date_add(@date1 , interval 4 day)),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1)) 
        when weekofyear(@date1) <> weekofyear(date_add(@date1 , interval 6 day)) then concat(year(date_add(@date1 , interval 5 day)),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1)) 
        else concat(year(date_add(@date1 , interval 6 day)),'-', if(weekofyear(@date1) < 10, '0','') ,weekofyear(@date1))  end as week_of_year;

> 2013-01

SQL Fidle 4

Now i either need to find an more elegant way of do it, or find a suitable strategy to include this in the group by condition. I was thinking in add an extra column to the table being grouped and -- after add an index --, group by week_of_year or by the week_start_date.

Does someone experienced have a better idea/strategy?

Notes: this is to be used in a database with over half million users, to Analise a certain action they perform and the group by condition will take other parameters (such as, but not limited to, demographics).

What do you actually want to end up with? If you just want the first day of the (Monday-Sunday) week containing the given input, you can simply do `date - INTERVAL WEEKDAY(date) DAY`; for weeks beginning on any other day, simply adjust by the relevant number of days. To obtain the year and week number, why not simply use [`YEARWEEK(date)`](http://dev.mysql.com/doc/en/date-and-time-functions.html#function_yearweek), `DATE_FORMAT(date, '%X-%V')` or `DATE_FORMAT(date, '%x-%v')`? — eggyal, Jul 24 '13 at 10:50
All what you need to know about that: [Getting first day of the week in MySql using Week No](http://stackoverflow.com/questions/3317980/getting-first-day-of-the-week-in-mysql-using-week-no) — valex, Jul 24 '13 at 10:52
@eggyal try to do select yearweek('2013-01-01'), weekofyear('2013-01-01') and see if they return the same week... — SQL.injection, Jul 24 '13 at 10:56
They will if you supply to `YEARWEEK()` the optional ***`mode`*** argument with a value of `3`, as indicated under the definition of [`WEEKOFYEAR()`](http://dev.mysql.com/doc/en/date-and-time-functions.html#function_weekofyear): "*`WEEKOFYEAR()` is a compatibility function that is equivalent to `WEEK(date,3)`.*" That is, try `YEARWEEK('2013-01-01', 3)`. — eggyal, Jul 24 '13 at 10:58
@eggyal yearweek, indeed, works. Many thanks, this will give me the week start: date select date_sub(@date2 , interval weekday(@date2) day); :) — SQL.injection, Jul 24 '13 at 11:10

Get the start date of a MySQL calendar week (weekyear)

0 Answers0