MySQL date/time query to a server in the WRONG timezone

Joseph_Sliker

Boy, I never thought I'd wanna do something this sophisticated: so even though I'm in the midwestern US, I went with a website host located in sunny California.

Okay here is the problem. Say I set an appointment time in a mysql table, in a date/time field.

The appointment time (SHOWTIME) is, say '2010-03-07 17:30:00' (5:30pm local time here in Milwaukee).

Now, I want to do a query of appointments like this:

SELECT if(SHOWTIME > Now(), 1, 0) as APPT, NOTES FROM showings ORDER BY SHOWTIME

This way, if the value of APPT is less than the system time (Now()) I can use the resulting value of 0 as a signal to color the resulting row in the displayed table, grey, or otherwise signal that the appointment time has passed. BUT the system, being in California is two hours behind me, and therefore the lines don't show up the way I want them until two hours later. Is there an arguement that can be put in the MySQL query that would correct this?

Thanks.

NogDog

Assuming it is MySQL, you can execute this query when you first connect and before any other queries (both inserts and selects):

mysql_query("SET time_zone = 'US/Central'");

And also set your PHP time zone:

date_default_timezone_set('America/Chicago');

Joseph_Sliker

Thanks for the reply. I'm not sure I'm doing it right: is the command:

mysql_query("SET time_zone = 'US/Central'");

...supposed to change something in the way "Now()" behaves: (it does not seem to).

I added the lines:

mysqli_query($link,"SET time_zone = 'US/Central'"); 
date_default_timezone_set('America/Chicago'); 
echo "Date/Time in Milwaukee is: " .date('Y-m-d H:i:s')."<br>";
$rightnow=date('Y-m-d H:i:s');

...up near the top of the file, and then use the $rightnow value in the query

$showings_sql="SELECT if(SHOWTIME > '".$rightnow."', 1, 0) as X, concat('<form style=\"margin-bottom:0;\" method=\"POST\" target=\"_self\"><input type=\"hidden\" name=\"THECHOSEN\" value=\"',showing_ID,'\"><input type=\"submit\" name=\"ACT\" value=\"EDIT\" CLASS=\"SubmitRed\"></form>') as EDIT, DATE_FORMAT(SHOWTIME,'&#37;a, %M %e, %l:%i %p') AS 'DAY/TIME', concat(..blahblahblah..) as PROPERTY, SHOWEDBY, showings.STATUS, COMMENT, showing_ID FROM showings LEFT JOIN import on showings.CCRE_ID=import.CC_ID ORDER BY SHOWTIME";

...and now I get the results I need. (But am wondering if I did something wrong by needing to use my $rightnow value in the query instead of Now())

NogDog

I may have misunderstood the MySQL manual concerning the time_zone setting, and suspect you should not normally have to worry about it. It may be an issue if you are using TIMESTAMP type columns, though. See http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html for what I was looking at.

Even if it does turn out that you need to use the set time_zone, not that per the manual:

The MySQL installation procedure creates the time zone tables in the mysql database, but does not load them. You must do so manually using the following instructions. (If you are upgrading to MySQL 4.1.3 or later from an earlier version, you can create the tables by upgrading your mysql database. Use the instructions in Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”. After creating the tables, you can load them.)