Welcome to WebmasterWorld Guest from 54.162.232.51

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

converting db UTC timestamps to user timezones

     
7:42 am on Sep 9, 2013 (gmt 0)

Preferred Member

10+ Year Member

joined:May 21, 2004
posts: 385
votes: 5


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');
10:18 am on Sept 10, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 7, 2003
posts:4783
votes: 0


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.
10:28 am on Sept 10, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2002
posts:18903
votes: 0


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.
11:21 pm on Sept 11, 2013 (gmt 0)

Preferred Member

10+ Year Member

joined:May 21, 2004
posts: 385
votes: 5


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.
2:57 am on Sept 12, 2013 (gmt 0)

Preferred Member

10+ Year Member

joined:May 21, 2004
posts: 385
votes: 5


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?
5:00 am on Sept 12, 2013 (gmt 0)

Preferred Member

10+ Year Member

joined:May 21, 2004
posts: 385
votes: 5


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);
}
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members