Time zone support

[globetrottingmike]

Hi,

 

I have developed a php/mysql application which I want my members to be able to see say forum posts in their local time zone. Their time zone info would be stored in the members table of the database, so a member living in Paris would have a +1 hr time zone difference stored in their profile from my server in London.

 

I have worked out from loads of searching that the following piece of script can adjust the current time (NOW) by adding or subtracting what is stored in that database field:

 

function getLocalTime($tm, $offset){ 
  return ($tm - date("Z", $tm)) + (3600 * $offset); 
} 


// get current local time in bangkok 
echo date("H:i:s d-m-y", getLocalTime(mktime(), $row_rs_timezone['difference'])). "<br>"; 

 

And I know that this line of code shows the date of the entry from the database data:

 

<?php echo date ("d M Y",strtotime($row_wall ['wall_date'])); ?>

 

How can I know marry the two up? I want to retrieve the date and time from the database item, say the forum post, and then use the first script I showed to calculate the difference?

 

Many thanks in advance

[xtopolis]

Can you perhaps approach it differently using this function: CONVERT_TZ?

[globetrottingmike]

Can you perhaps approach it differently using this function: CONVERT_TZ?

That looks great for the database, I am guessing that would change all dates in the database? How can I use in my php page. One of my members may be located in Paris, while another member may be located in New York, while another member in Sydney. They all need to see the timestamp stored in the database +or- their time difference from GMT.

[globetrottingmike]

Would it be something like this in php:

 

<?php function convert_tz ($date_str, $tz) ?>

<?php echo convert_tz('2008-08-08 08:08:08', +2)

 

only it does not seem to work.

[xtopolis]

I've looked into this more:

 

I've read that for dealing with timezone sensitive things, a common method is to store the time values in Epoch time (Unix time,seconds since jan 1, 1970).

 

Here are some threads:

http://forums.devnetwork.net/viewtopic.php?f=19&t=92892

http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/

 

Or you can store the times in GMT/UTC time (+0) and use the convert_tz function (WHICH IS A MYSQL FUNCTION) when pulling them.

SELECT postdate AS 'EST', CONVERT_TZ(postdate,'+00:00','-3:00') as 'PST' FROM testtest

Which would pull the same column twice, showing the original date (my server is located in EST time, and my equivalent date in PST local time)