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

Question

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....

George Reith · Answer 1 · 2012-01-01T16:57:01.603

1

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

edited Jan 01 '12 at 16:57

answered Jan 01 '12 at 16:34

George Reith

13,132
18
79
148

Thanks, but I'm afraid I don't quite follow. I think I understand how this could help if I wanted to store the data according to the client's timezone. But I don't understand how this can help me retrieve data that has been stored in UTC format, and convert that to the client's timezone. Could you explain a bit more? Would I need to change how I store my data (store the timestamp instead of the date and time?) Thank you. – user418775 Jan 01 '12 at 16:43
@user418775 It would be better to store it as a timestamp yes, however you can convert a date to timestamp like so `mktime ($hour, $minute,$second,$month,$day,$year,$is_dst)` more information here: http://php.net/manual/en/function.mktime.php – George Reith Jan 01 '12 at 16:51
mktime would work - I'd just need to iterate through the rows returned by the MySQL query and reformat the data. BUT there's a complication. Certain data entries appear to be on different dates if you compare the UTC time to the clients local time. For example "2 2011-12-31 17:11:49", "1 2011-12-31 18:19:35", "2 2012-01-01 03:46:12". (The 1st digit in each string is the location) When I convert this to EST the 1st and 3rd entries both happen on Dec 31st. As such, they need to be grouped together, however the MySQL query won't group them together since it was based on UTC time. – user418775 Jan 01 '12 at 17:16
using php to regroup the data would get a little cumbersome. Any chance you could help explain how I can solve my issue with the query itself? Thank you very much. – user418775 Jan 01 '12 at 17:17
@user418775 you can't do that with an MYSQL query as whether or not an event occurred on the same day depends on the timezone of the user and it is not possible to convert the data in the table anyway without pulling it out and using plain PHP. If you knew a specific day you wanted to query you could create a timestamp from the maximum and minimum time between that day or days and search for timestamps between them which would return anything that happened on a single date for whichever timezone you make the utc timestamps from. (If you stored timestamps instead that is) – George Reith Jan 01 '12 at 17:20
Thanks again. I've just read this: http://stackoverflow.com/questions/952975/how-can-i-easily-convert-dates-from-utc-via-php and found this comment interesting 'the manual says: "The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval."' – user418775 Jan 01 '12 at 17:28
That comment makes me wonder if I could get the query to group the data correctly if I were to store the data as TIMESTAMP. But I'm not sure if a runtime command exists to tell MySQL the client's timezone when I do the query. I really appreciate your help. Sorry if I sound like I'm being rather thick. – user418775 Jan 01 '12 at 17:34
I think I will try using mtkime along with reordering the data in php, as necessary. Thanks again. – user418775 Jan 01 '12 at 18:19
I used info from this discussion to get to my final solution, which I put in an answer to my own question. Thanks. – user418775 Jan 02 '12 at 16:32

score 1 · Accepted Answer · answered Jan 02 '12 at 16:30

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).

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

2 Answers2