Link to home
Start Free TrialLog in
Avatar of kennethw1
kennethw1

asked on

Convert database date/time into user timezone (auto detect)

I know theres a way to retrieve user's timezone using javascript.
Instead of converting it based on Greenwich Mean Time, i wish to convert the date and time that are stored within my database into the timezone of the viewer. I have been doing research for awhile and yet to find an example that matches my situation.

Lets say all the date/time that are stored within my database are stored in GMT and the current user is in GMT+2.  I wish to convert it the date stored into GMT+2 which is by adding 2 hours into the existing date.

Please advice. (PHP/Javascript prefered)
Avatar of ed987
ed987

<script>
var timeZoneOff=(new Date()).getTimezoneOffset();

function setCookie( name, value, expires/*days*/, path, domain, secure )
{
var today = new Date();
today.setTime( today.getTime() );
if ( expires ){ expires = expires * 1000 * 60 * 60 * 24; }
var expires_date = new Date( today.getTime() + (expires) );
document.cookie = name + "=" +escape( value ) +
( ( expires ) ? ";expires=" + expires_date.toGMTString() : "" ) +
( ( path ) ? ";path=" + path : "" ) +
( ( domain ) ? ";domain=" + domain : "" ) +
( ( secure ) ? ";secure" : "" );
}
setCookie("tzoff",timeZoneOff,100,"/","","");
</script>

that should set $_COOKIE['tzoff'], in minutes, don't divide by 60 because not all timezones are whole numbers.
ASKER CERTIFIED SOLUTION
Avatar of ed987
ed987

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ed that's so cool!

I'm more of a php guy than a javascript guy myself, so I'm impressed.

I just want to chime in real quick with how you turn a MySQL timestamp into a time in the user's local zone based on the cookie that ed showed us how to set. It's super easy:

Your MySQL timestamp will be in the format "Y-m-d H:i:s". You can convert that to a Unix timestamp (and from there to any other format you might need by using php's date function) by using strtotime:

$hour_mod = (int)$_COOKIE['tzoff'] >= 0 ? "+$_COOKIE[tzoff] hours" : "$_COOKIE[tzoff] hours";

$time = strtotime("$time_from_mysql $hour_mod");
Avatar of kennethw1

ASKER

Hey ed987, i dont really see where can i slot in the database date-time into the codes you made. Could you show me some direction?
$dt=date("Y-m-d H:i:s",time(0)+((double)$_COOKIE['tzoff'])*3600);

insert into ... values('$dt')
Ohh you probably got it wrongly. Im trying to retrieve an existing date-time value from the mysql and convert it accordingly to the timezone of the user that is browsing the page. Does your method work on first load as well?
um...

$hour_mod = (int)$_COOKIE['tzoff'] >= 0 ? "+$_COOKIE[tzoff] hours" : "$_COOKIE[tzoff] hours";
$time = strtotime("$time_from_mysql $hour_mod");

$time_from_mysql is just a placeholder for your timestamp stored in your database table
Mind putting everything together? I am bad at code when its some lengthy one.
How do you read from your database right now? I don't know what specific structure you use, but usually it works something like:

mysql_connect($db_server, $db_user, $db_pass);
mysql_select_db($db_name);
$query = "SELECT * FROM table WHERE field = 'value' LIMIT 1";
$row = mysql_fetch_assoc(mysql_query($query));
$time_from_mysql = $row['timestamp_field'];
$hour_mod = (int)$_COOKIE['tzoff'] >= 0 ? "+$_COOKIE[tzoff] hours" : "$_COOKIE[tzoff] hours";
$time = strtotime("$time_from_mysql $hour_mod");

And then of course the code for getting the timezone is just placed inside the head tags of the first page a user would visit when they arrive at your site.

What is the purpose of this script? Are you pulling many records from the database, or just one (which is what the script above assumes)?
First page a user would visit? Will it work on first visit? The purpose of the script is to display a list of competition date and i would like the date/time to be formatted in the visitor's timezone automatically.

eg:
Competition 1
Starting on: 2007-10-30 23:00:00

Competition 2
Starting on: 2007-10-25 19:00:00

Competition 3
Starting on: 2007-10-10 05:00:00

(These date-time value will be stored in GMT time) (Its like a feed)
The cookie will be persistent, so it should work ok with just one visit. The code ed uses to call his function will create a cookie with a life of 100 days. You can increase or decrease that if you want. The 3rd parameter to the function is the cookie lifetime in days. Sending 0 will cause the cookie to expire when the user closes his browser window.

Show me the code you're using to display the competition dates from the database and I'll show you how to modify your script to return those times in the user's local time zone.
Just assume that the column name is 'tourdate'. I'll fix it from there.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Everything seem to work well but its not working when the code line is included in another page.

It works perfectly when i tried it as a standalone.