Paginator sort on afterFind field

Question

I'm trying to show a list of our client's various time zones and their local time that is derived from that. Right now in my Client model I have the following function (doAfterFind) that is just a wrapper around afterFind that gives a consistent format.

public function doAfterFind($result = array()) {
    $result = parent::doAfterFind($result);

    if (!empty($result['timezone'])) {
        App::uses('CakeTime', 'Utility');

        $result['local_time'] = CakeTime::format(
            VIEW_DATE_FORMAT,
            strtotime('now'),
            null,
            $result['timezone']
        );
    }

    return $result;
}

The local_time field is correctly placed in the model after the find(). I have tried to implement this as a Virtual Field as well because it will need to be sorted. The problem is I can't figure out how to calculate the time based off the current time and client's timezone. I have tried the same in the model's constructor and adding it to the virtualFields array, but the time zone is not available during the constructor call - since there is no ID loaded or anything.

Does anyone know a way to either have MySQL calculate this, or use what I already have but as a virtual field compatible with Paginator sort()? I could format the date display in the View, but need the local time column to be sortable.

Notes: My time zones are stored as the locale name (America/New_York, etc...). The constant VIEW_DATE_FORMAT is just the date() format string F jS, Y - g:i A.

Try this function http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz in a virtual field. — floriank, Sep 03 '14 at 16:10

score 0 · Accepted Answer · answered Sep 03 '14 at 16:22

Assuming you have shell access to the server you're working on, see this regarding setting up your mysql server to be able to do timezone conversions based on timezone names: http://blog.mozilla.org/it/2012/11/16/converting-timezone-specific-times-in-mysql/

Once you have that set up, you should be able to make a virtual field:

$this->virtualFields['local_time'] = "CONVERT_TZ(NOW(), 'UTC', Client.timezone)";

(If your server is not set to UTC, you'll have to replace the second argument with your server's timezone.)

The result will probably not be formatted the way you want it, but you can do the formatting in afterFind.

Don't have shell access, since it's currently on a shared host, but at least I'm on the right track now. Thanks Kai! This will work great once I get the timezone tables loaded somehow. — Kyle Weishaupt, Sep 03 '14 at 17:03

Paginator sort on afterFind field

1 Answers1