[RESOLVED] Confused about getting a query by weeks

rprincejr

Im trying to figure out how to write a query to get results by weeks.

For example, If I wanted to see the results for this week, I would need to find the beginning of the week and the end of the week.

The query Im wanting to build will only show the current week, but it will change dynamically as days go by. So whatever day you look at the list, it will grab that weeks worth of results.

I think I can use the date('w') to get the day of the week and then either subtract it to 0 or add it to get the sum of 6 and then use those counts to modify the start and end dates.

Or can date('W') be used where it gives me the week of the year? I dont know about using that one because I wouldnt be able to get any dates.

Anyone have any ideas to start me correctly?

sneakyimp

Time handling can get really weird sometimes. [man]strtotime[/man] is your friend as it handles a lot of this weirdness. I'm guessing that when you say 'the current week' then you are talking about the time from just after midnite last nite to exactly seven days later.

i'm also assuming that your database table has a field that indicates the date of each record. let's assume it's called theDate and is a unix timestamp.

try something like this:

$today_date_string = date('Y-m-d');
$midnite_today_date_string = $today_date_string . " 00:00:00";
$start_unix_timestamp = strtotime($midnite_today_date_string);
$end_unix_timestamp = strtotime($midnite_today_datr_string . " +7 days");

$sql = "SELECT * FROM myTable WHERE theDate >= $start_unix_timestamp AND theDate < $end_unix_timestamp";
// etc.

rprincejr

Thats close but not quite. While that does get me a 7 day increment (week) for the results, you are setting the beginning of the 7 day period as today. It should be set to Sunday.

NogDog

SELECT * FROM the_table WHERE DATE_FORMAT(the_date, '%Y%u') = DATE_FORMAT(CURDATE(), '%Y%u')

Change %u to %U if you want the week to start on Sunday instead of on Monday.

sneakyimp

There's NogDog showing me up again 😢

rprincejr

I dont see how that works. So Nog's query sets the week by getting the current date and then backing up to monday or sunday based on the parameter given? If thats true, then I can just increment it at the end by 7 days to get my start and end.

And again, then I should be able to set the beginning and end as aliases to use them in the output.

Ill do some testing.

sneakyimp

This should be helpful MySQL Date and Time Functions

EDIT: NogDog's query will force the database to scan every row in your table which probably is fine for tens of thousands of records but if you get into the hundreds of thousands you'll need to find a different approach.

NogDog

The intent of my query (which may not match your actual intent if I've misunderstood it) is to match all records where their date formatted as the year followed by the week number (0 - 53) matches the Year-week of the current date. So if you ran the query today Wednesday, 9 OCT 2007, it would match for any date between Monday, 7 OCT 2007 and Sunday, 13 OCT 2007, as they would all have the same week number as today's date.

If what you actually want is everything from today through the next 6 days (for a total of 7 days), then you would want something like:

SELECT * FROM table_name 
WHERE the_date BETWEEN CURDATE() and DATE_ADD(CURDATE(), INTERVAL 6 DAY)

sneakyimp

I'm not certain but I think that query will be better as it will avoid a full table scan.

rprincejr

I coded this and it works for what i need. What do you guys think?

$today = date('Y-m-d'); 
$d = date('w', $today);
$d2 = 7-$d;
$bw = date('Y-m-d', strtotime("-".$d." day"));	 
$be = date('Y-m-d', strtotime("+".$d2." day"));

SELECT * FROM table WHERE date >= '$bw' AND date <= '$be'

One thing I needed was to be able to use the weekstart and weekend dates in my output.

Ex.
You are now viewing results from xx/xx/xxxx to xx/xx/xxxx.
-result
-result
-result

With my above code I accomplish that, but Im not sure how intensive it is on the database.

sneakyimp

Let's see...

today is tuesday, $d is 2
$d2 is 5.

$bw would be 2 days ago (sunday)
$be would be 5 days from now (sunday)

that sounds pretty good.

your sql clause should probably have one >= and the other < rather than <= or you get too many days?

rprincejr

I know theres always more than one way to skin a cat. I was just trying to figure out the easiest. You guys did help alot, and I hope I didnt offend any. It was a good discussion IMO.

This is now marked resolved.