CODEWITHSUNDEEP
phpmysqlsql
tom
tom

Reputation: 7

MySQL - selecting the correct data within the correct week using week beginnings

I am trying to select data, when inserting the data it has an auto insert of the date when submitting. So when data is inserted it inserts the current date. However, in my table I have week beginnings, so I am trying to select the data inside of that week:

mysql> select * from week;
+---------+------+------------+
| week_id | week | date       |
+---------+------+------------+
|       1 | 1    | 2014-12-29 |
|       2 | 2    | 2015-01-05 |
|       3 | 3    | 2015-01-12 |
|       4 | 4    | 2015-01-19 |
|       5 | 5    | 2015-01-26 |
|       6 | 6    | 2015-02-02 |
|       7 | 7    | 2015-02-09 |
|       8 | 8    | 2015-02-16 |
|       9 | 9    | 2015-02-23 |
|      10 | 10   | 2015-03-02 |
|      11 | 11   | 2015-03-09 |
|      12 | 12   | 2015-03-16 |
|      13 | 13   | 2015-03-23 |
|      14 | 14   | 2015-03-30 |
|      15 | 15   | 2015-04-06 |
|      16 | 16   | 2015-04-13 |
|      17 | 17   | 2015-04-20 |

e.g. 

select * from table where date='2015-04-06';

However the data will not be selected and presented because the inserted date was 2015-04-10. The only way to retrieve that data is by doing this: 

select * from table where date='2015-04-10'; < when the data was inserted

So my question is, is it possible to select that data from that week beginning? So if I select data from 2015-04-06 it should show data from the range of 2015-04-06 to 2015-04-12, is that possible? Hopefully I have explained correctly, been a bit tricky to explain let alone try to implement. I can add any more info if needed. NOTE: I am trying to use this inside of PHP so where the date is I would just use a variable, just thought I would say.

Upvotes: 0

Views: 78

Answers (4)

O. Jones
O. Jones

Reputation: 108676

I have found that the most robust way to do this sort of week processing is to truncate each date in the table (in your example 2015-04-10) to the preceding Monday at midnight. That way you can compute the week of each item by assigning it to the first day of that week.

This little formula returns the preceding Monday given any DATE or DATETIME value.

 FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -2, 7))

For example, 

 set @datestamp := '2015-04-10'
 SELECT FROM_DAYS(TO_DAYS(@datestamp) -MOD(TO_DAYS(@datestamp) -2, 7))

yields the value 2015-04-06.

So, if you have a table called sale you can add up sales by week like this:

 SELECT SUM(amount) weekly_amount,
        FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -2, 7)) week_beginning
   FROM sale
  GROUP BY FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -2, 7))

This is a very convenient way to handle things, because it's robust over end-of-year transitions. The WEEK() function doesn't work quite as well.

If your business rules say that your weeks begin on Sunday rather than Monday, use -1 rather than -2, as follows.

FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -1, 7))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Assuming that week is the same value as week(), the:

select t.*
from table t
where week = week('2015-04-10');

Even if the numbers do not match, then presumably you have some base date (such as 2015-01-01 and simple arithmetic would accomplish something very similar).

Upvotes: 0

TavoloPerUno
TavoloPerUno

Reputation: 579

You could also compare the week of the date the data was entered with the weeks in the week table using WEEK() function.

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

As the week will always end 6 days from the beginning you can use the between operator and the date_add function like this:

(for your specific example):

select *
from table
where date between '2015-04-06' and date_add('2015-04-06', interval 6 day)

And using a php variable:

select *
from table
where date between '$name_of_dt_var' and date_add('$name_of_dt_var', interval 6 day)

Upvotes: 1

Related Questions