Handling Timezone Offset

Suppose you have a PHP/MySQL app with clients in many timezones. Each client has a timezone config set. Your tables have an updated_at timestamp field which is maintained in the clients time, and you want to display that in a report. You want to make sure that the field displays properly in the user’s browser, taking into account that the user may not be in the same timezone as the configured value.

Another option is to maintain updated_at in GMT, but in our scenario you inherited the data situation as it is.

config.php

<?php
  $time_zone = 'America/Mexico_City';

Any time you do a save you do something like:

baseModel.php

<?php
  public function save() {
    ...
    $this->updated_at = date('Y-m-d H:i:s', time());
    ...

Now when you pull data for the report you want to do the following:

someReport.php

<?php
  ...
  $tzo = getTzOffset($time_zone); // time zone offset string for the site
  ...
  $columns['updated_at'] => array(
    'select' => 'DATE_FORMAT(pm.updated_at, "%Y/%m/%d %H:%i:%s ' . $tzo . '")',
    'type' => 'datetime',
    'title' => 'Updated At'
  );

And in a helper somewhere you need to return the offset string, such as ‘-5:00′:

helper.php

<?php
  function getTzOffset($time_zone)
  {
    $origin_dtz = new DateTimeZone($time_zone);
    $secs = $origin_dtz->getOffset(new DateTime);
    $gmd = gmdate('G:i', abs($secs));
    $sign = $secs < 0 ? '-' : '+';
    return $sign.$gmd;
  }

Now, in the json results from the query you get a value like:

  {updated_at:2014/10/01 21:27:09 -5:00, ...}

And in the javascript that handles formatting the data for the report you want:

formatter.js

  ...
  switch(format) {
    case 'datetime':
      var y = new Date(value);
      new_result[key] = y;
      break;
  ...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>