14

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?

TerranRich
  • 1,263
  • 3
  • 20
  • 38
  • 2
    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. – Marc B Apr 17 '12 at 02:15

4 Answers4

21

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');
deceze
  • 510,633
  • 85
  • 743
  • 889
  • This code is the best to suit my needs. However, I think it might be better to store timezones in a manner such as this: http://blog.benkuhl.com/2009/12/timezone-list-in-mysql/ with the offsets specified. It's a better way of presenting the choices to the user. With timezones stored that way, and if I know the server's timezone (which seems to be CDT), how can I use the known offset (e.g. -5.0, 3.0) on the stored `TIMESTAMP` values in the database? – TerranRich Apr 18 '12 at 23:06
  • 1
    @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. – deceze Apr 18 '12 at 23:12
  • 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 http://stackoverflow.com/questions/1727077/generating-a-drop-down-list-of-timezones-with-php would be the way to go about generating the list for the user? – TerranRich Apr 18 '12 at 23:19
  • I believe the answer to http://stackoverflow.com/questions/4755704/php-timezone-list may have what I need. Thank you! – TerranRich Apr 18 '12 at 23:45
  • 1
    @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())`. – deceze Apr 19 '12 at 00:11
  • I will do exactly that. Thank you! – TerranRich Apr 19 '12 at 02:09
1

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.
dotancohen
  • 30,064
  • 36
  • 138
  • 197
  • 5
    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. – Jack Apr 17 '12 at 03:12
0
$timezone = new DateTimeZone('America/Vancouver');

$date = new DateTime(date('m/d/Y h:i:s a', time()));
$date->setTimeZone($timezone);
echo $date->format('l F j Y g:i:s A')."\n";

Replace new DateTime(date('m/d/Y h:i:s a', time())); with new DateTime("UTC Time");

You can create a new DateTimeZone() object for each user input.

Jack
  • 5,680
  • 10
  • 49
  • 74
0

The way to do it is by using javascript. I think the best way to do it is by storing the users GMT into his cookies, and retrieving it on the PHP process form.

<script language="javascript">

function TimeZoneCookie()
{
 var u_gmt = (-(new Date().getTimezoneOffset()))/60;
 var o_date = new Date("December 31, 2025");
 var v_cookie_date = o_date.toGMTString();
 var str_cookie = "utimezone="+u_gmt;
 str_cookie += ";expires=" + v_cookie_date;
 document.cookie=str_cookie;
}
//---------------------
TimeZoneCookie();

</script>

u_gmt explained:

  1. Date().getTimezoneOffset() returns the offset to GMT-0 in minutes
  2. Since getTimezoneOffset() will return the offset to GMT-0 and not from GMT-0 we'll need to turn it around. How? simple, just by knowing that -*-=+ & -*+=-. If you know basic math, you already know this principle.
  3. As I said in step 1 getTimezoneOffset() will return the offset in minutes, so we just divide it by 60, so we can get the gmt offset format.

Result: (-(new Date().getTimezoneOffset()))/60

Now retrieve the cookie in PHP:

<?php

$user_timezone = $_COOKIE['utimezone'];

?>
RandomGuy
  • 338
  • 3
  • 9