Calculate user’s time using TimezoneOffset

Question

Hi, I am trying the get the user’s local time to store into my database. I cannot use PHP function now() as it returns the server’s time.

I have got the TimezoneOffset by using JavaScript:

d = new Date();
alert(d.getTimezoneOffset()/60);

How can I calculate the time by using this? And what type should I use for MySQL data type?

Thanks

Answer 1

Why don't you use PHPs now() and just convert it when displaying the time to the user. That way all your stored times are standard, and you won't have to do any trickery to display the same time to other users.

I found this handy little snippet of code for converting between your server time and the users time. If you were to setup and store the users local and your servers local you could easily make this a dynamic one size fits all approach.
http://www.tek-tips.com/viewthread.cfm?qid=1444820&page=1

//set local timezone
date_default_timezone_set("Europe/Paris");

//get server time as a unix timestamp
$unixtime = strtotime($servertime . ' EST'); //append EST to denote New York time

//convert to human readable local time
echo 'server time is '.date('r', $unixtime);

Answer 2

First store the value of d.getTimeZoneOffset() (without dividing) in your table as an INT.

Then, when getting the timezone, run:

SELECT `time` AS original_time, DATE_ADD(original_time, INTERVAL `usersettings`.`timezoneoffset` MINUTES) AS new_time FROM `times`

(Note: this is untested!)

Answer 3

I'd have to recommend storing all your times in UTC, then also storing the user's timezone offset. This allows for maximum flexibility, and some decent separation between actual data (UTC time) and display logic (UTC +/- Offset).

For storage, no matter how many times I try RDBMS specific time fields, unix timestamps in an int field always offer the best flexibility and portability.

You could store the user's timezone offset in seconds to make the process even simpler. So EST (-5) would become -18 000

Then, for displaying time -/+ user's offset, simple second maths will work just fine:

$now = time();
$userTime = $now + $user->getTimezoneOffset();

This'll work fine because adding a negative number is just like subtracting a positive one.

Edit:

You will be able to format the user's timestamp using PHP's standard gmdate() function. Just pass the calculated timestamp as the function's second parameter. See http://ca2.php.net/manual/en/function.gmdate.php

Whoops... date() is current locale aware. should use gmdate() instead.

There is of course, one totally different approach which may be better: Store a textual representation of the User's timezone which is compatible with PHP's date_default_timezone_set() . Then, on each request, if you have an authenticated user, you can set timezone, and all date functions will obey it.