Subtracting 8 hours from server time

[jumphopper]

Hey guys I'm having trouble with my server. My server is set in UK time (8 hours ahead) and I want to display my results in localtime (pacific time). I have a results page that posts all the variables in localtime by subtracting 8 hours from the server time. however, for some reason it seems to automatically add random variables to my 'end' over and over again at random times.

Can someone please review my code to see what can be causing this multiplication of my 'end' and if I'm using my "DATE_SUB(NOW(), INTERVAL 8 HOUR)" correctly?

Thanks in advance for all your replies. 🙂

Here's my code:

<?php
require('sql.php');


$sq = "SELECT id 
FROM `africa_tracker` 
WHERE MONTH(timespan)=MONTH(NOW()) 
    AND DAY(timespan)=DAY(NOW()) 
    AND YEAR(timespan)=YEAR(NOW()) 
    AND HOUR(timespan)=HOUR(NOW()) 
LIMIT 1"; 
$res = @mysql_query($sq); 


if(!$r) 
{ 
    // No rows yet, we need to add the initial one 
	$q = "INSERT INTO `africa_tracker` (start, end, replay, s_africa, botswana, ethiopia, tanzania, kenya, send_friend, wallpapers, ipod, site_link, africa, antarctica, australia, europe, asia, egypt, n_america, s_america, india, return, call, timespan) 
	VALUES ('','1','','','','','','','','','','','','','','','','','','','','','', DATE_SUB(NOW(), INTERVAL 8 HOUR))";

} 
else 
{ 
    // Already a row dealing with the current hour 
    // Specify Update query 
    $q = "UPDATE `africa_tracker` SET end=end+1 WHERE id='".$r['id']."'"; 
} 
// Define which action we're taking (Update or Insert); 
$action = substr($q, 0, strpos($q, ' ', 0)); 
$action = ucwords(strtolower($action)); 

$res = @mysql_query($q); 
if(!$res) 
{ 
    // Failed to update/insert 
    die('Failed to '.$action.' value(s) in <em>africa_tracker</em>.  MySQL gave the following information:<br><strong>Error #:</strong> '.mysql_errno().'<br><strong><em>Error  :</em></strong> '.mysql_error()); 
} 


?> 

[MarkR]

What you should really be doing, is rather than adding / subtracting a certain number of hours, is simply set the time zone for the page to where you want to be.

For example, try the following

         putenv("TZ=America/Los_Angeles");

Then see how the date functions behave.

I believe a list of time zones is somewhere in the PHP docs.

I find that it's a good idea to store all date/time values in the database in GMT, then calculate the local time zone time for display. This is such that if you're in a time zone with daylight saving, or you ever want to change time zone, the times in your database don't change.

Mark