Problems with NOW() and timezone

jackandblue

I've got a band website, and I'm using PHP/MySQL to pull info from a database for our performance schedule. I want the page only to display our shows from the current date forward, not any shows that have already happened. To make this happen, here is my SELECT statement:

"SELECT * FROM dates WHERE date >= NOW() ORDER BY date ASC";

This seems to work perfectly except for one thing: the NOW() command seems to use GMT (Greenwich Mean Time), while I'm in the US/Eastern Time Zone. It's a 5 hour difference (4 hours with DST). That means that at 7:01 PM on the night of a show, my page no longer displays today's show, because it's tomorrow in GMT (have I totally confused everyone now?). It's been a real problem for our fans, because they come to our site to get directions to a show, and it's no longer listed.

Any suggestions?

Here's a link to the page:

http://www.mikeestepband.com/dates.php

statrat

/*$current should be the timezone on the
server, $target is the timezone
you want to calculate.  Both should be in
hours.  For example, GMT -05:00 should
be -5.  GMT +12:00 should be 12.  You can
use the returned timestamp with the date
function.

ex: echo date('r', zonechange(-5, 10));
would echo the date for a +10:00
timezone on a machine in -05:00.
*/

function zonechange ($current, $target) {
$current = -1 * $current;
$zonedate = mktime(date('G'), date('i'), date('s'), date('n'),
date('j'), date('Y'), 1) + (($current + $target) * 3600);
return $zonedate;
}

jackandblue

statrat -

Not sure exactly how to implement the code you suggested. This SELECT statement is in the head of my document, and the NOW() is the only time I reference any dates or times.

Can you be a little more specific on exactly how to use the code you suggested? Sorry for sounding clueless, but my brain is complete mush at the moment.

Thanks!

statrat

i guess that was overkill. you know, you can do date arithmitic on the now() function, so try something like this:

SELECT FROM dates WHERE date >= (NOW()-(53600)) ORDER BY date ASC";

jackandblue

Sorry, statrat, that didn't seem to work. It returned all the shows in the database, regardless of date.

Any other suggestions?

statrat

what type/format is your dates field? i'm sure you're familiar with it, but have you messed around with these functions
http://www.mysql.com/doc/en/Date_and_time_functions.html

i think this should work if date is a datetime field.

SELECT * FROM dates where date>=date_sub(now(), INTERVAL 5 hour)

short of that, you could select your date and compare it on the php side with its date/time functions or find someone who knows more than i do.

jackandblue

Thanks, statrat, this seems to be working! It's around 10:30 and a show posted with today's date is still showing up. Very cool.

I'll just have to check it in the morning to make sure it's gone.

Great, I really appreciate the help. No more complaints from the masses. Cool!

www.mikeestepband.com

boylecp

Just wondering where u got the buttons for your site from?
Thanx.

jackandblue

boylecp:

If you're talking about the navigation buttons on the left, I made them. I used Illustrator to build the shape, then Photoshop to add the text, the red ends, and the glow around the outside.

Glad you like them.