• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

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)
0
kennethw1
Asked:
kennethw1
  • 6
  • 5
  • 3
2 Solutions
 
ed987Commented:
<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.
0
 
ed987Commented:
<script>
var timeZoneOff=((new Date()).getTimezoneOffset())/(-60);

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>

<?
if(isset($_COOKIE['tzoff']))
{
  echo gmdate('Y-M-d h:i',time(0)+((double)$_COOKIE['tzoff'])*3600);
}
else echo "refresh this page";
?>

( "don't divide by 60 because" = ment "dont divide and round" )
0
 
MasonWolfCommented:
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");
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kennethw1Author Commented:
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?
0
 
ed987Commented:
$dt=date("Y-m-d H:i:s",time(0)+((double)$_COOKIE['tzoff'])*3600);

insert into ... values('$dt')
0
 
kennethw1Author Commented:
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?
0
 
MasonWolfCommented:
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
0
 
kennethw1Author Commented:
Mind putting everything together? I am bad at code when its some lengthy one.
0
 
MasonWolfCommented:
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)?
0
 
kennethw1Author Commented:
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)
0
 
MasonWolfCommented:
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.
0
 
kennethw1Author Commented:
Just assume that the column name is 'tourdate'. I'll fix it from there.
0
 
MasonWolfCommented:
$hour_mod = (int)$_COOKIE['tzoff'] >= 0 ? "+$_COOKIE[tzoff] hours" : "$_COOKIE[tzoff] hours";
$time = strtotime("$result[tourdate] $hour_mod");
$datestring = date('Y-m-d H:i:s',$time);
0
 
kennethw1Author Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now