CODEWITHSUNDEEP
mysqlsqlweek-number
Sean
Sean

Reputation: 269

MySQL Select Week Range From Database

In my database I am storing week numbers and the relating years. For example something like this:

id | week | year | text
---+------+------+-----------
 1 |  23  | 2012 | hello
 2 |  27  | 2012 | something
 3 |  39  | 2012 | more text
 4 |  43  | 2012 | more text

Then on the front end the user can select a week and a year and I then want to be able to return all records between that range. For example selecting between week 25 - 2012 and week 50 - 2012 would return the last 3 records.

I'm not sure whether it will be easier to also hold the first date of the week as well and search on this or if there is a way to search on weeks easily.

Thanks in advance.

Upvotes: 0

Views: 1168

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Presumably, you want consecutive weeks, even when the year is different. To do this, some arithmetic helps:

select text
from dates
where year*100 + week between $weekmin+$yearmin*100 and $weekmax+$yearmax*100

This converts, say, week 7 in 2012 to "201207" for comparison purposes.

Upvotes: 4

Floris Velleman
Floris Velleman

Reputation: 4888

SELECT * 
FROM yourtable
WHERE week >= User_input1_week
AND week <= User_input2_week
AND year >= User_input1_year
AND year <= user_input2_year

This would probably work.

I'm not sure whether it will be easier to also hold the first date of the week as well and search on this or if there is a way to search on weeks easily.

Using the DATE type for your column would probably be a better move.

Upvotes: 0

Tom&#225;š Zato
Tom&#225;š Zato

Reputation: 53338

Well since MySQL supports comparison operators this task is really easy:

SELECT text FROM `dates` WHERE week>=$weekmin AND week<=$weekmax AND year>=$yearmin AND year<=$yearmax ORDER BY year DESC, month DESC

Upvotes: 1

Related Questions