converting db UTC timestamps to user timezones

[mihomes]

I am storing timestamps in my db using UTC_TIMESTAMP(). My plan is to let users select their timezone from their account which will then correctly output any queries/output of these values.

At the moment this only involves last login, current login, and a forgotten password timestamp, but will include more down the road.

I decided on storing as UTC in the db so everything is constant and will allow for easy conversion to what the user has specified for their account... or so I think.

Questions :

When doing comparisons with current timestamp can I rely on gmdate('Y-m-d H:i:s')? For instance...

if ($row['forgotExpire'] > gmdate('Y-m-d H:i:s'))
{
}

where forgotExpire would be 0000-00-00 00:00:00 format from the db.

Also, any advice on how to go about correct output for users? I came across this which I can use on the website for users to select their tz :

$timezones = DateTimeZone::listAbbreviations();

$cities = array();
foreach( $timezones as $key => $zones )
{
  foreach( $zones as $id => $zone )
  {
    /**
     * Only get timezones explicitely not part of "Others".
     * @see http://www.php.net/manual/en/timezones.others.php
     */
    if ( preg_match( '/^(America|Antartica|Arctic|Asia|Atlantic|Europe|Indian|Pacific)\//', $zone['timezone_id'] ) 
  && $zone['timezone_id']) {
      $cities[$zone['timezone_id']][] = $key;
  }
  }
}

// For each city, have a comma separated list of all possible timezones for that city.
foreach( $cities as $key => $value )
  $cities[$key] = join( ', ', $value);

// Only keep one city (the first and also most important) for each set of possibilities. 
$cities = array_unique( $cities );

// Sort by area/city name.
ksort( $cities );

So, that selected value could be stored in the db of course then just convert using something similar to :

$tz = new DateTimeZone('America/Los_Angeles');

$date = new DateTime('Thu, 31 Mar 2011 02:05:59 GMT');
$date->setTimeZone($tz);

echo $date->format('F j, Y @ g:i:s a e');

[swa66]

Storing time&date in the database in UTC is by far the smartest thing to do.
you then have 2 options:
- Either you convert it to the user's timezone in PHP
- Either you convert it to the user's timezone in mysql: [dev.mysql.com...]
Note there's a system timezone, and a per connection timezone.

[g1smd]

Storing all data in UTC is the correct approach. Make sure that the UTC data is being correctly stored both sides of a DST change at the server location. I've seen many occasions where the UTC data was out by one hour for half the year.

Be aware that many, but not all, users are also subject to DST changes and that these will be on different dates to that experienced by the server, indeed spread over more than a month at each change. Additionally, countries in the Southern hemisphere add the hour six months out of step with the Northern hemisphere.

[mihomes]

Thanks for the replies. I really want to stick with UTC stored on the server at all times then just modify any output upon display to the users defined tz in their account.

Can you elaborate on the storing both sides of the dst? I see what you are saying, but don't see where it would become an issue as past times (those stored in the db) would not change only current ones which I would be pulling at that moment in their set tz to store.

[mihomes]

Having an issue during conversions that I cannot figure out. I am storing timestamps in my db using UTC_TIMESTAMP().

I want to output them based on user selected timezone. I tried writing a small function to do this, however, it does not output correctly.

// Date/time converter
function convertTZ($date, $tz, $tzFormat)
{
$date = new DateTime($date);
$date->setTimezone(new DateTimeZone($tz));

return $date->format($tzFormat);
}

echo $_SESSION['dtCurrLogin'].'<br />';
echo convertTZ($_SESSION['dtCurrLogin'], 'UTC', 'F j, Y @ g:i:s a e');


dtCurrLogin from db = 2013-09-12 01:23:45

the above outputs :

2013-09-12 01:23:45
September 12, 2013 @ 5:23:45 am UTC

Obviously this is not correct as I went from UTC to UTC so they should be equal. If I change to output EST then it shows 1:23:45 am, but of course that would not be right either. What am I missing here that is causing wrong conversions?

[mihomes]

Didn't realize I needed to specify my original timezone (utc in my case). Using this function works for me :

function convertTZ($date_time, $from_tz, $to_tz, $format_tz)
{
$time_object = new DateTime($date_time, new DateTimeZone($from_tz));
$time_object->setTimezone(new DateTimeZone($to_tz));
return $time_object->format($format_tz);
}