Won't work on saturday...

mrbaseball34

I have this query that will not return any records if I run it on Saturday.

select g.gamenum,
       g.gamedate,
       g.homeid,
       h.teamname home,
       g.homescore hscore,
       g.visitorid,
       v.teamname visitor,
       g.visitorscore vscore
 from games g,
      teams v, teams h
 where (g.homeid = h.id and g.visitorid = v.id) 
   and (WEEK(g.gamedate) = WEEK(CURDATE()))
   and ( g.homescore is not null and g.visitorscore is not null  )
   and (h.division = "L" or v.division = "L")

What do I need to change to make it return the games for the
entire week INCLUDING saturday?

drawmack

I've never had a need to use the week function of sql. You say it works apropriatly except when you run it on a saturday and that on saturday it returns no records. Saturday is the last day of the week, maybe that is what's causing the problems. Try this:

SELECT WEEK(gamedate), WEEK(CURDATE()) FROM games WHERE ID=<id of some game in the week you want)

run that on a saturday and print the results to see what they are, it could be that your server is in a different timezone then you are and therefor it is rolling over to the next week befor you expect it to.

mrbaseball34

The TZ's may be a problem, I'll have to check it out.

However. I'd like to be able to run it on Saturday and it compile the games snce the last Sunday.

What am I doing wrong that is giving me null results?

olly2111

well uve asked on valcatohostings forum so u cant do much else however i think they are UK based.

mrbaseball34

Yes I did, and they weren't much help.
Since they are 5 hours ahead, this should work:

select g.gamenum,
       g.gamedate,
       g.homeid,
       h.teamname home,
       g.homescore hscore,
       g.visitorid,
       v.teamname visitor,
       g.visitorscore vscore
 from games g,
      teams v, teams h
 where (g.homeid = h.id and g.visitorid = v.id) 
   and (WEEK(g.gamedate) = WEEK(CURTIME()-5))
   and ( g.homescore is not null and g.visitorscore is not null  )
   and (h.division = "L" or v.division = "L")

But in phpmyAdmin, it returns weird results...

mrbaseball34

If I do this in phpmyAdmin, it returs 14 and 37...

select WEEK(g.gamedate), WEEK(CURTIME()-5) from games g

mrbaseball34

select g.gamenum,
       g.gamedate,
       g.homeid,
       h.teamname home,
       g.homescore hscore,
       g.visitorid,
       v.teamname visitor,
       g.visitorscore vscore
from games g,
     teams v, teams h
where (g.homeid = h.id and g.visitorid = v.id) 
  and (WEEK(g.gamedate) = [color=red]WEEK(DATE_SUB(CURDATE(), INTERVAL "5 HOURS"))[/color]
  and (g.homescore is not null and g.visitorscore is not null)
  and (h.division = "L" or v.division = "L")

Help, please????? Problem area is in red...

drawmack

Hmmm,

I would revert to php. Get the timezone of the user in php. Then use that to calculate the difference for the time zone to the server. Then just pass the appropriate date into the query instead of relying on the server to do it for you.

mrbaseball34

I KNOW what my TZ is, it will always be CDT.

How would I calculate the value to pass to the query?

mrbaseball34

Well, how's this look. I won't be able to test it until next Saturday.

Well, that's not entirely true. I'll be able to test it tonight when I
get home and am able to modify the time on my development
server.

Thanks for the tip drawmack, I never would have thought of
doing it that way...

$sql = $sql.'select g.gamenum,';
$sql = $sql.'       g.gamedate,';
$sql = $sql.'       g.homeid,';
$sql = $sql.'       h.teamname home,';
$sql = $sql.'       g.homescore hscore,';
$sql = $sql.'       g.visitorid,';
$sql = $sql.'       v.teamname visitor,';
$sql = $sql.'       g.visitorscore vscore';
$sql = $sql.' from games g,';
$sql = $sql.'      teams v, teams h';
$sql = $sql.' where (g.homeid = h.id and g.visitorid = v.id) ';
$sql = $sql.'   and (WEEK(g.gamedate) = '.gmstrftime ("%W", time()-18000);
$sql = $sql.'   and ( g.homescore is not null and g.visitorscore is not null  )';
$sql = $sql.'   and (h.division = "L" or v.division = "L")';

mrbaseball34

On my local system,

WEEK(g.gamedate) returns 19

but

gmstrftime ("%W", time()-18000); returns 18

If I run it from my host's server, whose time is set to GMT -1, both
return 18.