UTC Problems, not sure how to fix them (PHP/MySQL)

Question

I am seeing UTC date and times in all of my date/time fields on my server, I followed it all back to the timezone setup on my webserver being in UTC time... my question is how can I make these into the local users date and time? All the times are stored in UTC on the MySQL server.

I have the users City, Region (Prov/State) and Country. Worst case I want to display the dates and times in the PHP default time zone.

How do I do this?

Answer 1

In MySQL you can use the function CONVERT_TZ to convert all the dates to the current time zone when you select the data from the db. Another stackoverflow thread discusses how to convert time zones using PHP 5's DateTime class.

Answer 2

You could use this function, and pass it the user's location "Perth, WA, Australia" as the address.

Instead of returning 0 on failure, you could have it return the offset between the server timezone and the default php timezone.

You may want to store this in a session variable, and only call the function if the variable is empty, which would improve the speed of your page renders.

/**
 * This function finds the geocode of any given place using the geocoding service of yahoo
 * @example getGeoCode("White House, Washington");
 * @example getGeoCode("Machu Pichu");
 * @example getGeoCode("Dhaka");
 * @example getGeoCode("Hollywood");
 *
 * @param   string address - something you could type into Yahoo Maps and get a valid result
 * @return  int timeZoneOffset - the number of seconds difference between the user's timezone and your server's timezone, 0 if it fails.
 */
function getTimeZoneOffset($address) {
    $_url = 'http://api.local.yahoo.com/MapsService/V1/geocode';
 $_url .= sprintf('?appid=%s&location=%s',"phpclasses",rawurlencode($address));
 $_result = false;
 if($_result = file_get_contents($_url)) {
  preg_match('!<Latitude>(.*)</Latitude><Longitude>(.*)</Longitude>!U', $_result, $_match);
  $lng = $_match[2];
  $lat = $_match[1];

        $url = "http://ws.geonames.org/timezone?lat={$lat}&amp;lng={$lng}";
     $timedata = file_get_contents($url);
     $sxml = simplexml_load_string($timedata);
     $timeZoneOffset = strtotime($sxml->timezone->time) - time();
     return $timeZoneOffset;
    }
    else
    return 0;
}

Code adapted from Time Engine, a LGPL php class.