CODEWITHSUNDEEP
phpmysqltimezone
user418775
user418775

Reputation: 698

Convert date and time from UTC to client's local time on server with MYSQL

I have a query that produces a result like this:

desired output

The data is sorted by date DESC, then by time DESC, but also the common 'markers_name' elements are chunked together. (They're only chunked together for each date).

To do this I get the list of MAX(time) values for every combination of (markers_id, date) in conditions, then join that list to the row set I am getting from the present query, and use the MAX(time) values for sorting:

SELECT
  m.name AS markers_name,
  c.time AS conditions_time,
  c.date AS conditions_date,
  s.name AS station_name
FROM markers m
  INNER JOIN conditions c ON c.markers_id = m.id
  INNER JOIN station s    ON c.station_id = s.id
  INNER JOIN (
    SELECT
      markers_id,
      date,
      MAX(time) AS time
    FROM conditions
    GROUP BY
      markers_id,
      date
  ) mx ON c.markers_id = mx.markers_id AND c.date = mx.date
ORDER BY
  c.date DESC,
  mx.time DESC,
  m.name DESC,
  c.time DESC

The date and time is stored in the MySQL database as UTC time. The client uses javascript to convert the date and time to the local time zone. Instead I'd like to do the conversion on the server with php and MySQL. Can anyone offer any suggestions on how I might accomplish this with a MySQL query (or perhaps with php and MySQL)?

The client uses ajax to request this data from the server so I can easily include timezone offset in the query using the javascript method getTimezoneOffset().

The date is stored into the database using separate date and time fields using this code:

// convert timestamp to seconds
// time is provided by client as the number of milliseconds since January 1, 1970 00:00:00 UTC.
$utc_timestamp = trim($_POST['utc_timestamp']);
date_default_timezone_set('UTC');
$utc_timestamp = $utc_timestamp/1000;
$time = date("H:i:s",$utc_timestamp);
$date = date("Y:m:d",$utc_timestamp);

If necessary I could store $utc_timestamp instead of $time and $date, but it's a little scary to change the way I've been storing my data.

Thanks in advance....

Upvotes: 0

Views: 2782

Answers (2)

user418775
user418775

Reputation: 698

This is how I solved my issue with php.

The client gets the Timezone offset with javascript:

var date = new Date();
var utcOffset = -date.getTimezoneOffset()/60;

The client sends the offset to the server via ajax and the server fetches the offset:

$utcOffset = isset($_REQUEST["utcOffset"]) ? $_REQUEST["utcOffset"] : 0;
$pieces = explode(".", $utcOffset);
$hours =  $pieces[0];

// handle '.5' because some timezones include a half hour portion
if (sizeof($pieces) == 1)
  $minutes = 0;
else
  $minutes = '.' . $pieces[1];

// convert .5 to 30  
$minutes *= 60; 

if ($hours[0] == '-')
  $minutes = '-' . $minutes;

The server queries the database (this is shown in my original post). Then my php iterates through the results and produces xml, which is sent back to the client. Within that loop, I have placed this code, which converts the date and time from UTC to the client's local time:

$time = date('g:i a', strtotime($hours . ' hours,' . $minutes . ' minutes', strtotime(date($row['conditions_date'] . $row['conditions_time']))));

$date = date('D, M j, Y', strtotime($hours . ' hours,' . $minutes . ' minutes', strtotime(date($row['conditions_date'] . $row['conditions_time']))));

So in the database I have this date '2012-01-01' and this time '20:22:11'. The code above converts converts the time to '3:22 pm' and the date to 'Sun, Jan 1, 2012' when the client provides -5 (which is the value for Eastern Standard Time).

Upvotes: 1

George Reith
George Reith

Reputation: 13476

You can use date_default_timezone_set() in conjunction with localtime($utc_timestamp) or date($utc_timestamp) to convert the UTC timestamp to a timezone of your choosing.

A list of accepted paramaters (timezones) for date_default_timezone_set() is found here:

List of supported timezones

This will still require you to use some sort of client side script (javascript) to retrieve the user's timezone as this information is not sent to the server by the client.

I suggest you should change to storing $utc_timestamp as it is a constant measure of time that you can compare any two strings with. It makes it much easier in the future say if you want to compare the age of different entries .etc

Upvotes: 1

Related Questions