MySQL fun

[Bonegavel]

I want to create a query that, based up the date given (YYYY-MM-DD) it should return the results from Monday - Saturday of the given week. If the date given is monday, then that is the start of that week.

something like

select (c1+c2+c3) as TotalStuff
from tableX
where tableX.date >= MondayOfGivenDate and tableX.date <= SaturdayOfGivenDate

Somehow the MondayOfGivenDate and SaturdayOfGivenDate take a date value in the form YYYY-MM-DD and calcs out the monday and saturday of that week.

Working with date/time is a pain in the tuckus.

[cuervo72]

I'm not the best with dates/timestamps wither, but I wonder if you could benefit by using the UNIX_TIMESTAMP function somewhere.

[Bonegavel]

select dayname("2007-11-28")

obvsioulsy gives me Wednesday... hmmmm, I may have to create a select statement in there

select(GivenDate)
case "Monday"
GivenDate = GivenDate
break

case "Tuesday"
GivenDate = GivenDate - 1
break

...

Is that heading in the right direction? If so, I have to find out how to add a select statement in a query :)

[Fighter of Foo]

You'll probably want/need to import a comparision table that has all of the saturdays and mondays predefined (hard-coded) and use that instead.

[Bonegavel]

I'm not the best with dates/timestamps wither, but I wonder if you could benefit by using the UNIX_TIMESTAMP function somewhere.

my dates are in the MySQL type Date format (YYYY-MM-DD).

<!-- Results table body --> UNIX_TIMESTAMP("2007-11-28") = 1196226000 and I can convert back with from_unixtime(1196226000)

Just thinking outloud here....

[Subby]

I was led to believe there would be fun in this thread.

[Bonegavel]

oops, my select above was meant to be a switch statement.

[cuervo72]

Well, just found something out about mysql...apparently it doesn't like <= and >= for timestamps, but it *does* like between, like so:

SELECT * FROM `some_table` WHERE TimeStamp between '2007-11-27 22:06:48' and '2007-11-28 22:06:48'

As for what your start and end dates are, I'd figure those out programatically. If you're using php, the date function is a good start probably: http://us3.php.net/date .

[Radii]

what if the date is sunday?

[Bonegavel]

what if the date is sunday?

The user won't be clicking on sunday but in case they would, I will show them the monday right after since the little date picker goes from Sun-Sat they may accidentally click on Sunday instead of Monday.

[cuervo72]

This might be useful too:
http://us3.php.net/manual/en/function.getdate.php#71650

[Radii]

This isn't going to be syntactically correct, but I'm thinking something like this:

GivenDate is the parameter that comes in

x = DayOfWeek(GivenDate) - 2 /* DayOfWeek returns an index, 1=Sunday, 2=Monday, etc */
StartDate = DATE_ADD(GivenDate, INTERVAL -x DAY) /* Subtract the difference between monday... this is why I asked about Sunday, Sunday would be -1, I dunno how INTERVAL works precicesly but I'm hoping it would end up adding a day (minus negative 1) */
EndDate = DATE_ADD(StartDate, INTERVAL 5 Day)

Select * from Table
Where Date BETWEEN StartDate and EndDate

I dunno MySQL Syntax but logically I believe this gets you what you want. Take the index of the day of the week of the given day and compare it to monday. Set a start date based on that commparasion. Add 5 days from start(which should be a monday now) to get your end date, use those vars for simple select.

[Radii]

dola, DayOfWeek and INTERVAL stuff I got from:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[Radii]

double dola, Cuervo is almost certainly right, if you're using php, I would assume there are date utilities in php that make it much simpler to figure out the date range that you can use in your query.

[Radii]

I was led to believe there would be fun in this thread.


triple dola!! Are you kidding Subby, this IS fun :D

[lordscarlet]

So was this solved, or do I need to go back and actually read the thread and help out? :)

[Bonegavel]

I'm not convinced it's solved.

I think I need to test for the day of the week I submit and then subtract days based on that to get to monday.

Edit**

since PHP/MySQL handle dates differently (for the most part) it is pain to deal with them.

I have DATE types in mysql that are YYYY-MM-DD and I need to get the previous monday and then saturday.

Anyone know how I can do a switch statement inside a MySQL query?

[lordscarlet]

Should be something like:


select
(c1+c2+c3) as TotalStuff
from tableX tx
where
tx.date >= date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day) --Monday
AND tx.date <= date_add(date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day), interval 6 day) -- Saturday

where '2007-11-28' is the given date.

[lordscarlet]

dola: possibly overly verbose syntactically, but it should work based on the initial problem you gave.

[Bonegavel]

Should be something like:


select
(c1+c2+c3) as TotalStuff
from tableX tx
where
tx.date >= date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day) --Monday
AND tx.date <= date_add(date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day), interval 6 day) -- Saturday
where '2007-11-28' is the given date.

ran this and i got

<code> #1054 - Unknown column 'Monday' in 'where clause' </code>

[lordscarlet]

ran this and i got

<code> #1054 - Unknown column 'Monday' in 'where clause' </code>

sorry, remove the "-- Monday" and "-- Saturday". That's MS SQL style comments, I guess they're different in MySQL -- or if you know the proper format, change the "--" to however you comment in MySQL :)

[Bonegavel]

sorry, remove the "-- Monday" and "-- Saturday". That's MS SQL style comments, I guess they're different in MySQL -- or if you know the proper format, change the "--" to however you comment in MySQL :)

Ah, and that worked perfectly for a hard coded date.

I want to send in any given day of the year and have the query figure out monday and saturday on-the-fly and then do a sum on some tables.

My last resort is to just make the person select monday on the date picker, but I wanted to have it figure out your current week on-the-fly.

[lordscarlet]

Ah, and that worked perfectly for a hard coded date.

I want to send in any given day of the year and have the query figure out monday and saturday on-the-fly and then do a sum on some tables.

My last resort is to just make the person select monday on the date picker, but I wanted to have it figure out your current week on-the-fly.

Yes, that will work, just replace the hardcoded date with your date.

Is there something I'm missing?

[cuervo72]

You really want to use php to find the right timestamps, and then feed them into the sql. Something like this:

$sql = "SELECT * FROM `some_table` WHERE UNIX_TIMESTAMP(tx.date) between $mon_timestamp and $sat_timestamp";

Use something like this to figure out the days:
$mon_timestamp = mktime(0, 0, 0, date("m") , date("d") - (date("N")-1), date("Y"));
$sat_timestamp = mktime(0, 0, 0, date("m") , date("d") + (6 - date("N")), date("Y"));

I haven't thought this through because of the kids screaming in my ear, but I think php.net should give you enough to go on here (do some legwork, man!).

[lordscarlet]

You really want to use php to find the right timestamps, and then feed them into the sql. Something like this:

$sql = "SELECT * FROM `some_table` WHERE UNIX_TIMESTAMP(tx.date) between $mon_timestamp and $sat_timestamp";

Use something like this to figure out the days:
$mon_timestamp = mktime(0, 0, 0, date("m") , date("d") - (date("N")-1), date("Y"));
$sat_timestamp = mktime(0, 0, 0, date("m") , date("d") + (6 - date("N")), date("Y"));

I haven't thought this through because of the kids screaming in my ear, but I think php.net should give you enough to go on here (do some legwork, man!).

I'm not really sure wha'ts wrong with using mysql here rather than php..

[cuervo72]

Ok, I suppose it can, but he has to somehow express this:

date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day)

With non-constants, yes? 2007-11-28 isn't going to be constant, and neither is "2 day". The former can be subbed out for the current date like you said. But isn't "2 day" going to vary depending on what day he's entering (0 for Mon, 1 for Tue, etc)? I suppose that would be DAYOFWEEK()?

[Radii]

Ok, I suppose it can, but he has to somehow express this:

date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day)

With non-constants, yes? 2007-11-28 isn't going to be constant, and neither is "2 day". The former can be subbed out for the current date like you said. But isn't "2 day" going to vary depending on what day he's entering (0 for Mon, 1 for Tue, etc)? I suppose that would be DAYOFWEEK()?

the input date is the only variable. 2 day is a constant, dayofweek returns an integer based on the day of the week. 1 = sunday, 2 = monday, 3 = wednesday, etc. You basically always want to 'subtract' monday from the dayofweek result to find out how far from Monday you are.

[Bonegavel]

my experience has been that using sql where you can (instead of PHP or whatever you are using) is far better.

[lordscarlet]

Ok, I suppose it can, but he has to somehow express this:

date_sub('2007-11-28', interval dayofweek('2007-11-28') - 2 day)

With non-constants, yes? 2007-11-28 isn't going to be constant, and neither is "2 day". The former can be subbed out for the current date like you said. But isn't "2 day" going to vary depending on what day he's entering (0 for Mon, 1 for Tue, etc)? I suppose that would be DAYOFWEEK()?

What I posted is constant. I only used '2007-11-28' because I don't know what field he is using. If he puts a field (or constant, as he mentions after your post) then it works fine. the "- 2 day" in there is tomodify it from Saturday to Monday, because the dayofweek is based on Saturday, not Monday, bein the first day. the confusion here is that I did it the way it works instead of putting some parens in.. that evaluates as:

date_sub('2007-11-28', interval (dayofweek('2007-11-28') - 2) day)

so you're modifiying 2007-11-28 by the int day of the week subtracted by 2. (and since you're subtracting, it's actually adding two days because bonegavel's week starts 2 days after mysql's week). But my understanding is that bonegavel is passing in a static date (that is, he'll pass a date into mysql to compare, not use a date dynamically populated for each row)

[cuervo72]

Ah ok, I see now.

[Bonegavel]

What I posted is constant. I only used '2007-11-28' because I don't know what field he is using. If he puts a field (or constant, as he mentions after your post) then it works fine. the "- 2 day" in there is tomodify it from Saturday to Monday, because the dayofweek is based on Saturday, not Monday, bein the first day. the confusion here is that I did it the way it works instead of putting some parens in.. that evaluates as:

date_sub('2007-11-28', interval (dayofweek('2007-11-28') - 2) day)

so you're modifiying 2007-11-28 by the int day of the week subtracted by 2. (and since you're subtracting, it's actually adding two days because bonegavel's week starts 2 days after mysql's week). But my understanding is that bonegavel is passing in a static date (that is, he'll pass a date into mysql to compare, not use a date dynamically populated for each row)

I will be supplying A date into this formula.

IOW, the user will, say, click on Today's date.

They will look at daily numbers about today's date. The web page is storing the fact that the date is 2007-11-29. This is passed from page to page as is unless changed and then that is passed from page to page since every query relies on a date.

Then, they have the option to click on the Weekly summary. The web page still knows it's today's date (2007-11-29) that is stored in a variable and then if they click on Weekly this date is fed into the query that will the have to figure out that the begining of the query is 2007-11-26 and the end is 2007-12-1.

It will be different for any given date they select.

If they select last Tuesday, the query would need to figure out that Monday was 2007-11-19 and Saturday was 2007-11-24.

this is why I thought it might be good to figure out the day of the given date which will then allow a switch statement to be able to get you to Monday and then, with addition, Saturday.

I don't know how to implement a switch statement in mysql and maybe I'll just have to write an if/else structure and I haven't done that before either.

my thoughts are

select *
from table
where
if (dayofweek(IncomingDate) == Monday){ c1 >= IncomingDate and c1 <= IncomingDate+5)
elseif (dayofweek(IncomingDate) == Tuesday){ c1 >= IncomingDate -1 and c1 <= IncomingDate+4)
elseif(dayofweek(IncomingDate) == Wednesday){ c1 >= IncomingDate -2 and c1 <= IncomingDate+3)
elseif (dayofweek(IncomingDate) == Thursday){ c1 >= IncomingDate -3 and c1 <= IncomingDate+2)
elseif(dayofweek(IncomingDate) == Friday){ c1 >= IncomingDate -4 and c1 <= IncomingDate+1)
elseif(dayofweek(IncomingDate) == Saturday){ c1 >= IncomingDate -5 and c1 <= IncomingDate)
else{c1 >= IncomingDate and c1 <= IncomingDate+5}


the last else would be for sunday and then I'd treat it like monday.

This is what I need, I think but I don't know how to do it in mysql. Date subtraction/addition is available in mysql so it wouldn't simply be IncomingDate +1, it would use Date_Add() and Date_Sub().

[Radii]

I don't know how to implement a switch statement in mysql and maybe I'll just have to write an if/else structure and I haven't done that before either.


This is what the interval/dayofweek(givenDate) calculations in lordscarlets example does for you... no need for all the conditionals I don't think!

[lordscarlet]

What Radii said -- my code should do what you're asking for. It should work for every day of the week without any modifications.

[Bonegavel]

What Radii said -- my code should do what you're asking for. It should work for every day of the week without any modifications.


oh, I'll try it again :)

[Bonegavel]

What Radii said -- my code should do what you're asking for. It should work for every day of the week without any modifications.

I'm a tool. That works and you are a brilliant mofo. Sweet jeezuz I would have never made it that elegant.

[Bonegavel]

Thanks for everyone's input in this thread!

[lordscarlet]

I'm a tool. That works and you are a brilliant mofo. Sweet jeezuz I would have never made it that elegant.

Thanks for everyone's input in this thread!

Glad we could help. :) Believe it or not the people that write databases and programming languages are way smarter than us and have built in methods to do most of what we want to do. :D

[Bonegavel]

Glad we could help. :) Believe it or not the people that write databases and programming languages are way smarter than us and have built in methods to do most of what we want to do. :D

Not enough of my job is programming (i'm trying to rectify that) but when I have bust something out it needs to have been completed a month ago, if you catch my meaning. What I'm doing now is fun but the time frame is crazy.

Every time I get bogged down in sql I swear that I will not rest until I can write sql in my sleep and every time I finish my sql project I let it drop. :) *sigh*