Programming for mysql with Native Timezone Support

The idea behind that is that you do not have to take time server time offset when programming for mysql.

Based on http://www.electrictoolbox.com/mysql-set-timezone-per-connection/ and  http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html.

Run once and then in cron:

10      3       *       *       *       /usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u[ADMIN_USER] -p[PASSWORD] mysql > /dev/null 2>&1

To test:

CREATE TABLE IF NOT EXISTS test_tz (id int(11) NOT NULL AUTO_INCREMENT,
when datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB ;
SET time_zone = 'America/Los_Angeles';
INSERT INTO test_tz VALUES (NULL, NOW());
SET time_zone = 'Europe/Kiev';
INSERT INTO test_tz VALUES (NULL, NOW());

and check the results you get in the table above.

The above shows to make use of date and time mysql commands bound to native timezone support per connection. And now, in your php code, when opening a mysql connection you can generally use something like:

$db = Zend_Db::factory($db_adapter, $params);
$db->getConnection();
$db->query("SET NAMES UTF8");
$db->query(sprintf("SET lc_time_names = '%s'", $options['db']['locale']));
$db->query(sprintf("SET time_zone = '%s' ", $options['db']['timezone']));
/*or: SET time_zone = '-8:00';*/

Very helpful, taken from a real Zend framework-based application.