PHP & MySQL: Converting Stored TIMESTAMP into User's Local Timezone

Question

So I have a site with a comments feature where the timestamp of the comment is stored in a MySQL database. From what I understand, the timestamp is converted to UTC when stored, then converted back to the default timezone when retrieved. In my case, my server is in the Central Daylight Time timezone (CDT).

I have a plan to get the timezone from each user via entry form. I just wanted to know how to convert the TIMESTAMP value into the user's timezone.

• First, would I convert from UTC to local timezone? Or CDT to local timezone?
• Secondly, how would I go about doing that in PHP? Would I just do:

$userTimezone = new DateTimeZone($userSubmittedTimezoneString);
$myDateTime = new DateTime($storedTimestamp, $userTimezone);

...or is that not correct?

Solution 1

Date/time/datetime values are stored in MySQL as you supply them. I.e. if you INSERT the string 2012-04-17 12:03:23 into a DATETIME column, that's the value that will be stored. It will be converted internally into a timestamp which may or may not be accurate (see below), but when you query for the value again, you'll get the same value back out; the roundtrip is transparent.

Problems may occur if you try to do time calculations inside SQL. I.e. any operation that requires SQL to take the timezone and/or the server time into account. For example, using NOW(). For any of those operations, the timezone and/or server time should be set correctly. See Time Zone Problems.

If that doesn't concern you and you only need to do calculations in PHP, you only need to make sure you know from which timezone to which timezone you want to convert. For that purpose it can be convenient to standardize all times to UTC, but it is not necessary, as timezone conversions from any timezone to any other timezone work just as well, as long as you're clear about which timezone you're converting from and to.

date_default_timezone_set('Asia/Tokyo'); // your reference timezone here

$date = date('Y-m-d H:i:s');

/* INSERT $date INTO database */;

$date = /* SELECT date FROM database */;

$usersTimezone = new DateTimeZone('America/Vancouver');
$l10nDate = new DateTime($date);
$l10nDate->setTimeZone($usersTimezone);
echo $l10nDate->format('Y-m-d H:i:s');

Solution 2

There is no reliable way to get the user's timezone. Timezone information is not sent in HTTP headers. The best that you could do is either:

1. Match the IP address againsta geographic database -or-
2. Use Javascript to get the time set on the user's computer and either send that to the server (AJAX) or make the time string on the client.

Comments

[Marc B]

no, timestamps are stored as-is in mysql, but without any TZ data. if you insert "3pm, CST" into the db, then 3pm, cst is what's stored. You'd need to convert to UTC before you insert so you have a common unchanging base to convert to other TZs.

[Jack]

He already has a way to get the timezone. He's asking the users to select the timezone they're in. What he wants is a way to convert the time to the specified timezone.

[Gromski]

@Terran You really want to use timezones by their named identifiers, not merely by an offset. Offsets change throughout the year based on local daylight saving regulations, and all of those are different. If the user chooses "DST -06:00" today, you have no idea which timezone that is and when it will change to "DST -07:00" or "DST -05:00". You have to let the user choose a named timezone. You can calculate and display the offset at the time the user chooses it if you want to.

[TerranRich]

OK, so when presenting the list box for users to choose from, the values sent to the database will be things like America/New_York... is there a way to display the current offset of America/New_York et al to GMT when giving them the option? Perhaps something like the answer to stackoverflow.com/questions/1727077/… would be the way to go about generating the list for the user?

[TerranRich]

I believe the answer to stackoverflow.com/questions/4755704/php-timezone-list may have what I need. Thank you!

[Gromski]

@Terran Well, please don't use the static list of that accepted answer. You can generate a list with the current offset using something like timezone_offset_get(new DateTimeZone('Asia/Tokyo'), new Datetime()).

[TerranRich]

I will do exactly that. Thank you!