storing timestamps meaningful to visitor's local time

sneakyimp

I'm working with with the facebook API to create a facebook app. When I get info about a user, it tells me the user's timezone:

Array
(
    [id] => 1234567890
    [name] => Hugh Jass
    [first_name] => Hugh
    [last_name] => Jass
    [link] => http://www.facebook.com/profile.php?id=1234567890
    [timezone] => -7
    [updated_time] => 2010-05-30T00:14:06+0000
)

When the user uses my app, my server can record a timestamp of any particular action or event. I'd like to know the most robust/elegant way to convert these timestamps to something meaningful to a local user. Nobody wants Greenwich time unless you live in Greenwich. My Los Angeles users want Pacific Time or whatever (which varies with daylight savings time).

Am I right in understanding that the [man]time[/man] function returns the current UTC/GMT timestamp? Am I also right in understanding that it is the [man]date[/man] function which converts that time stamp to the local time zone whereas gmdate simply converts a timestamp? This code seems to confirm that:

$now = time();
echo date('Y-m-d H:i:sP', $now); // 2010-06-02 18:08:38-05:00
echo gmdate('Y-m-d H:i:sP', $now); // 2010-06-02 23:08:38+00:00

// if you change the default timezone, they match:

// change timezone
date_default_timezone_set('UTC');
$now = time();
echo date('Y-m-d H:i:sP', $now); // 2010-06-02 23:12:01+00:00
echo gmdate('Y-m-d H:i:sP', $now); // 2010-06-02 23:12:01+00:00

I'm sort of puzzled about how to handle these timestamps which will be stored in a db due to the implicit time-zone related conversions that happen. I'm guessing that storing events with UTC timestamps is the way to go so I can handle timezone conversions more smoothly.

I expect I'll use datetime fields in the db. I will NOT use mysql's NOW function or CURRENT_TIMESTAMP or whatever. To capture the current UTC time in YYYY-MM-DD HH:MM:SS format would be done thusly:

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

To display the current time in the user's timezone seems a bit trickier. If any has hints about how to do this better, I would much appreciate it:

// let's assume $event_datetime was the UTC datetime when some event happened
$event_datetime = '2010-04-30 08:30:00';

// the user's timezone as reported from the FB api
$user_timezone = -7;

// does this work?
$local_timestamp = strtotime($event_datetime . ' ' . $user_timezone . ' hours');

This seems convoluted to me. More importantly, I'm wondering how it might be impacted by daylight savings.

If anyone has a better way or any insights, I'd love to hear about it.

NogDog

Look at [man]date_default_timezone_set/man, e.g.:

$tz = "Etc/GMT" . $fb_data['timezone']; // Assumes "-13" to "+13"
date_default_timezone_set($tz);
echo date('Y-m-d H:i:s');

sneakyimp

Thanks NogDog - I didn't realize you could define relative to GMT using using +13/-13 and date_default_timezone_set and I wasn't particularly looking forward to parsing out some meaningful mapping from +13/13 to the the dozens of possibilities for timezones

NogDog

I wasn't exactly sure it could be done, and had to poke around a bit until I saw those timezone names in the list of "other" timezone name. 🙂

sneakyimp

nice RTFM kung fu.

sneakyimp

OK so now I'm wondering the best way to deal with UTC datetime values stored in my db.

E.g.:

2010-06-03 12:05:31

This is the GMT/UTC datetime when a record was updated. It's recorded in the db in a MySQL DATETIME field. The reason I have chosen datetime is because i want to avoid any Y2K38 problems with timestamps and also because it's been my experience that timestamp values often undergo local time conversions by mysqldump.

If I want to convert that datetime (which is GMT/UTC) to the current local time, I have to convert it first to a unixtimestamp and then convert again by using default_timezone_set and the date function. This is again seeming pretty ugly.

// $row['some_datetime'] is from the db and has a value of 2010-06-04 03:33:24 which
// is the current UTC time as I post this


// $fb_data['timezone'] is -7 and comes directly from my FB user data

$utc_timestamp = strtotime($row['some_datetime'] . ' GMT'); // 1275622404
$tz = "Etc/GMT" . $fb_data['timezone']; // Etc/GMT-7
date_default_timezone_set($tz);
echo date('Y-m-d H:i:s', $utc_timestamp); // outputs 2010-06-04 10:33:24  -- WTF??

For some reason the date this returns is 14 hours in the future -- tomorrow morning 10AM my time. If I reverse the $fb_data['timezone'] to +7 then I get the current time here -- which is what I expected.

This seems like a lot of work to me. Surely there is a more elegant way.

EDIT: some cleanup

bradgrafelman

Why not do it in the query?

$sql = "SELECT col1, datetime_col + INTERVAL $user_offset HOUR AS datetime_col, ...";

sneakyimp

Thanks for the suggestion, bg. That looks better than anything I've tried yet. However:

1) It makes it harder to encapsulate things because I'll need to pass the user's time zone down into any class or method that I might create to fetch stuff from the DB. I'm currently looking at all my messy procedural code here and thinking about how hard it's getting to maintain. Time for some classes I think.

2) I still need to turn the datetime value returned from the db into a timestamp if I'm going to easily turn '2010-06-04 03:00:00' into June 4, 2010 at 3 AM.

It seemed like such a simple thing to store a UTC datetime to capture the time of a given event. No 2k38 issues. No timezone conversions for a mysqldump. I find it pretty crazy that I must do the following to get a UTC datetime from a db, add a timezone offset, and apply pretty date formatting.

// we must reverse the timezone's polarity because of the way the date
// function is working
function reverse_timezone($tz) {
	if ($tz == 0) return 0;

$sign = ($tz < 0) ? '+' : '-';

return $sign . abs($tz);
} // reverse_timezone()

// takes a utc_datetime and returns its utc_timestamp
// necessary because there is no gmstrtotime function
function utc_datetime_to_utc_timestamp($datetime) {
	if (!preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $datetime)) {
		throw new Exception('Invalid datetime supplied to utc_datetime_to_utc_timestamp()');
	}

$dt_parts = explode(' ', $datetime);
$d = explode('-', $dt_parts[0]);
$t = explode(':', $dt_parts[1]);
return gmmktime($t[0], $t[1], $t[2], $d[1], $d[2], $d[0]);

} // utc_datetime_to_utc_timestamp()


$tz = 'Etc/GMT' . reverse_timezone($facebook_user['timezone']);
date_default_timezone_set($tz);
$response['last_checkin'] = date('F j, Y \a\t g:i A', utc_datetime_to_utc_timestamp($utc_datetime));

Surely there is some more elegant PHP for this?

bradgrafelman

1) Where is the user's timezone preference held? Is it not accessible by the DB (e.g. in a users_prefs table or something)? If it is, then can't you let the DB handle looking up the user's offset and applying it to the time?

2) I doubt I'm understanding your entire datetime dilemmas (heck, I don't even fully understand my own similar dilemmas sometimes :p), but could you not use something like [man]strftime/man in PHP to explicitly state the format that the DB is going to be returning the date as?

Also, is there any benefit you're getting from converting the date using PHP? If not, can't you just use something like MySQL's DATE_FORMAT() instead?

sneakyimp

Thanks for responding BG. I appreciate your effort.

I do not store the user's timezone in my db. It comes from a call to the facebook API.

[man]strftime[/man] and nearly all of the other functions that do timestamp-to-date conversions (or vice versa) take the local timezone into account which I DO NOT WANT. My server is in the central time zone and this has no bearing at all on the situation at hand. I want to store a UTC time and convert it according to whatever timezone is handed to me from facebook's API. The only date/time functions I have found so far that do not use the current timezone setting are [man]gmdate[/man] (which requires a utc timestamp as input), gmmktime (which i'm using in my function above -- it requires me to string parse a datetime), and [man]gmstrftime[/man] (which also requires a timestamp).

Now you may suggest that I set my timezone to 'UTC' so that I can rely on one of the timezone-aware functions to convert my datetime to timestamp. That may work, HOWEVER when I try to use Nog's suggestion that I use a timezone such as 'Etc/GMT-7', the date functions calculate the offset in the wrong direction. By this I mean that instead of subtracting 7 hours from the utc timestamp to get the proper time in Los Angeles, it adds 7 hours to the utc timestamp giving me the time in...Bangkok? Hanoi? Jakarta? Krasnoyarsk? That is why I wrote the function above to reverse my timezone.

This code seems to work:

// === determine local time ===
// set default timezone to UTC to disable timezone conversions in date/time functions
date_default_timezone_set('UTC');
// not sure that fb timezones will have leading '+'
$clean_offset = ($facebook_user['timezone'] > 0) ? '+' . abs($facebook_user['timezone']) : $facebook_user['timezone'];
$local_timestamp = strtotime($user_visit_data['visits_utc_datetime'] . ' ' . $clean_offset . ' hours');
$local_time = date('F j, Y \a\t g:i A', $local_timestamp);

It also seems pretty simple. I'm not sure how I feel about changing the default time zone on my server temporarily to UTC. Something bugs me about it semantically. It is pretty economical though.

EDIT: I find myself still grumbling that nearly all of the date/time functions in PHP have this stealthy application of some current timezone value to a given var. It just so...communist.

Weedpacket

$now_local       =   date('Y-m-d H:i:s T',                           time());
$now_utc         = gmdate('Y-m-d H:i:s \U\TC',                       time());
$now_with_offset = gmdate('Y-m-d H:i:s +0700', strtotime('-7 hours', time()));
echo $now_local,"\n",$now_utc,"\n",$now_with_offset;

sneakyimp

I have managed to get a UTC timestamp and date_time as follows:

$current_unix_timestamp = time();
$current_utc_datetime = gmdate('Y-m-d H:i:s', $current_unix_timestamp);

That's not the hard part. The hard part is when I store $current_utc_datetime in a database field and then want to apply an offset to it later. there is no convenient function to take a $utc_datetime and a) apply an offset and b) format the result. This problem is that there doesn't seem to be a gm* function for datetime input.

Weedpacket

So the code I posted doesn't do the right thing in $now_with_offset?

sneakyimp

Unless I'm missing something, it looks like your $now_with_offset line requires a time() value as input. I want to take a utc datetime from my db and apply a time offset. The only built-in php function I've found that can create a timestamp without applying the current timezone to it is [man]gmmktime[/man], which requires that I string parse the date time first.

I want to store my events as UTC datetimes in my db. This avoids y2k38 problems and timezone conversions performed by mysqldump.