Same day of week query

[solsurfer]

I'm trying to write a myphp query which will show me same-day results for today's day of the week. For example, today is Tuesday, therefor show Today and all previous tuesdays. I can get each order's day of week by using "DATE_FORMAT( b.statusdate, '%a')", but what is the syntax for making this show where equal to today's day of the week.

Any help would be appreciated.

Thanks!

[coopster]

Welcome to WebmasterWorld, solsurfer.

SELECT DAYOFWEEK(myDateField), DAYOFWEEK(NOW()) FROM mytable 
WHERE DAYOFWEEK(myDateField) = DAYOFWEEK(NOW());

[dev.mysql.com...]

[solsurfer]

Thank you, this is perfect!

How would you further modify to restrict by time of day? For example, suppose you ran this query at noon. The query would show all revenue for today (Tuesday) compared to previous Tuesdays. However, it is comparing today's mid-day revenue to the previous Tuesday's full day. How can I restrict it so the time periods are the same, i.e. Tuesday through noon compared to previous Tuesdays through noon?

[freeflight2]

... WHERE DAYOFWEEK(myDateField) = DAYOFWEEK(NOW()) AND (HOUR(myDateField) < 12)

would restrict your query to 0.00am to 11.59:59 am before noon

PS: always make sure that your DB server's clock is in sync with your web or application server(s)

[solsurfer]

And if I make it

... WHERE DAYOFWEEK(myDateField) = DAYOFWEEK(NOW()) AND (HOUR(myDateField) < now())

that would restrict it to up to the present time?

[freeflight2]

should probably be:

... WHERE DAYOFWEEK(myDateField) = DAYOFWEEK(NOW()) AND (HOUR(myDateField) < 12) AND (myDateField < NOW())

[solsurfer]

This isn't what I had in mind. I think the problem is the "< 12". Noon was just an example - I want the previous days to be resticted to revenue through the time the report is run. So if I run it at 8 pm, it shows last Tueday's revenue through 8pm. Same if I run at 9pm, etc. Is that an easy tweak?

Thanks in advance for your help!

Rod

[ltboy]

SELECT DAYOFWEEK(myDateField), DAYOFWEEK(NOW()) FROM `mytable`
WHERE ( DAYOFWEEK(myDateField) = DAYOFWEEK(NOW()) )
AND (HOUR(myDateField) < HOUR(NOW()) );

Alternatively, If you don't wish to round down to the nearest hour then use:

SELECT DAYOFWEEK(myDateField), DAYOFWEEK(NOW()) FROM `mytable`
WHERE ( DAYOFWEEK(myDateField) = DAYOFWEEK(NOW()) )
AND (myDateField < NOW() );

[killroy]

For the last query you probably want to extract times only and compare those, I think it's something like this:

TIME(MyDateField)<TIME(NOW())

[solsurfer]

Itboy's first query worked, but as he said it rounds down to the hour. I tried his second query and it didn't limit the previous days. And I tried adding Kilroy's Time function in various ways but it resulted in an error.

How can I modify so that results are limited to the actual time and not rounded down?

Here's the actual query if that would help:

SELECT DATE_FORMAT(b.statusdate,'%Y-%m-%d, %a'), count(*), sum(payment)
FROM investreq a, reqstatus b
WHERE a.refnum = b.refnum AND b.status = 'ab'
AND a.curstatus NOT IN ('ab','fl','re','pp')
AND DAYOFWEEK(b.statusdate) = DAYOFWEEK(NOW())
AND (HOUR(b.statusdate)< HOUR(NOW()))
GROUP BY 1
ORDER BY 1 DESC

Thanks!

[ltboy]

Heh, Sorry, I was tired when I posted my first reply.

Ok, here's the deallio. To use TIME() you need MySQL 4.1.1+. Here's a quick fix that rounds to the nearest minute.

SELECT DATE_FORMAT(b.statusdate,'%Y-%m-%d, %a'), count(*), sum(payment)
FROM investreq a, reqstatus b
WHERE a.refnum = b.refnum AND b.status = 'ab'
AND a.curstatus NOT IN ('ab','fl','re','pp')
AND DAYOFWEEK(b.statusdate) = DAYOFWEEK(NOW())
AND (HOUR(b.statusdate)< HOUR(NOW()))
AND (MINUTE(b.statusdate)< MINUTE(NOW()))
GROUP BY 1
ORDER BY 1 DESC

If you want you can add the line

AND (SECOND(b.statusdate)< SECOND(NOW()))

in there to get more accurate, but, IMHO, nearest minute should be enough and will probably be lighter on the system resources.

[ltboy]

Oops again. You're trying to get data UP TO the current time... you need to change those < brackets to <= otherwise say it's 9:05, you'll only get data through 8:04. Heh :p