Query with timezone adjustments

[djones]

I moved my host to another company and they are 13 hours ahead. I'm in the east coast of U.S. and the server host is in Perth, Australia. One of my SQL lines needs to be adjusted to allow for the timezone change. This is the current code thats needs to be set to 13 hours behind the now() functiion. Can anyone please help?

[code]
$sql .= "WHERE text IS NOT NULL AND (y*10000+m*100+d) >= ";
$sql .= "(YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) "; // This line needs to be adjusted to my time which is now 13 hours behind
[/code]

[printf]

Use INTERVAL

[code]$sql .= "( (YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) - INTERVAL 13 HOUR )";[/code]

But I would look at using CONVERT_TZ() instead, surrounding each of your date functions...

If you don't want it to return datetime formart...

[code]2006-11-18 11:00:00[/code]

Then surround the above with with ( expression + 0 )

[code]$sql .= "( ( (YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) - INTERVAL 13 HOUR ) + 0 )";[/code]

which will return....

[code]20061118110000[/code]


printf

[djones]

I'm not getting any results. Here is the full query:

$sql = "SELECT id, y, m, d, title, text, start_time, end_time, TIME_FORMAT( start_time, '%l:%i%p' ) AS stime, TIME_FORMAT( end_time, '%l:%i%p' ) AS etime, ";
$sql .= "TIME_FORMAT(end_time, '%k:%i') AS etime, " . DB_TABLE_PREFIX . "users.uid, fname, lname ";
$sql .= "FROM " . DB_TABLE_PREFIX . "mssgs ";
$sql .= "LEFT JOIN " . DB_TABLE_PREFIX . "users ";
$sql .= "ON (" . DB_TABLE_PREFIX . "mssgs.uid = " . DB_TABLE_PREFIX . "users.uid) ";
$sql .= "WHERE text IS NOT NULL AND (y*10000+m*100+d) >= ";
$sql .= "( ( (YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) - INTERVAL 13) )";
//OLD LINE  $sql .= "(YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) ";
$sql .= "ORDER BY y, m, d, start_time limit 6";

This is supposed to list the next 6 upcoming events. ITworks with the OLD line, but not with the correct timezone help

[fenway]

*shudder*... y, m and d are what? and what is that ghastly expression with all those NOW()'s doing?

[printf]

Your missing the HOUR at the end

this...

[code]$sql .= "( ( (YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) - INTERVAL 13) )";[/code]

should be...

[code]$sql .= "( (YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW())) - INTERVAL 13 HOUR )";[/code]

printf

[djones]

Actually I had that, it was a copy n paste error in the post

[djones]

@Fenway - Yeah I know. This is an old calendar script. I'm terrible with date functions with any SQL. The y, m and d hold the year, month and day separate for sorting purposes I guess...

[printf]

what does (y, m, d) represent?

printf

[djones]

they are fields that hold the year, month and day. Here is sample data:

id uid m d y start_time end_time title                           text
1 1 6 24 2004 22:00:00 00:00:00 Dorian Grey - Rhino Dorian Grey @ Rhinoceros Club - 10pm - no cover

[djones]

I changed it to this and it seems to be working...

[code]$sql .= "( (YEAR(NOW())*10000+MONTH(NOW())*100+DAYOFMONTH(NOW() - INTERVAL 13 HOUR)) ) ";[/code]

[fenway]

Well, it's great that you got it working, but you should serious considering upgrading that script!