Converting stored times to a Timezone

azhar

Hello!

I'm having trouble finding a suitable solution to my problem, I hope you can help!

I have a mySQL database, with times and dates stored in it, in this format: 2001-08-01 21:50. What I've done is stored a cookie within users browsers of the time offset value from their region and London, England. The cookie has the offset stored as +5 for 5 hours ahead or -5 for 5 hours behind. What I would like to do is add this offset value of the cookie to my stored time in the database, for example once this is done 2001-08-01 21:50 would now look something like 2001-08-02 01:50 for a 5 hour ahead offset. I've tried a few methods, including spliting the stored times into seperate values like $hour, $minute, $date etc, and added the cookie value, like $hour+5. This has failed to work as with our example, the stored time: 2001-08-01 21:50, turns into 2001-08-01 26:50.

Does anybody have any ideas for a solution?

Thanks in advance!

Azhar Jassal

vex__

Have a read of this...

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Date_and_time_functions

Basically the you need to use either INTERVAL, or DATE_ADD

Say you have the cookie value in $offset, then you could write some PHP/SQL like this...

$sql = "SELECT DATE_ADD( db_date_value, INTERVAL $offset HOUR) FROM your_table";

and fire that at your database and it should give you your desired value back.. I can't test this as I don't have access to a MySQL database at the moment..

HTH,

Anon

Hi. This actually hasnt got anything 2 do wid ur problem. But i jus wana no if ur the same Azhar Jassal that im lookin 4. My long lost cuz. This is gonna look realy stupid 2 the people readin it, but if this is u then PLEASE PLEASE contact me via msn messenger. U neva cum on since we talked that day 4 the first time in 6 years. if u aint the guy im tryin 2 find then please ignore this message and my stupidity. Thank you.