geekdom without assumption

Timezone fun with MySQL and UTC

Since our company has timeclocks and users all over the country (and world, for that matter), we switched pretty early on to using UTC exclusively in the back-end. The views handle spitting out the correct representations respective to the users’ timezone, but the PHP is always thinking in UTC; naturally, that means that MySQL needs to think in UTC as well. MySQL defaults to using system time with no timezone data loaded. My understanding is that they do this on purpose since our wonderful legislators worldwide have a habbit of changing the timezone information (daylight savings, etc.) and there isn’t a real clean way of managing that for MySQL server admins.

At any rate, the switch is relatively painless, given you grasp the key concepts and have the HOWTO handy. I always find myself re-Googling this, so I’m putting it up here to do just that. There are only two steps to make the change as far as the DB is concerned–any software that is accessing may need help dealing with the new paradigm, but you’re on your own there.

1. Load the timezone data into the mysql database. This is accomplished with one line and a couple of pipes:
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
That should be all there is to it. If you get a couple of “Warning: Unable to load ‘/usr/share/zoneinfo/Asia/Riyadh87’ as time zone. Skipping it.” messages, you’re good to go. If you get some other error, check to see if your distro stores the zoneinfo file in a non-standard place.

2. Configure MySQL to start with UTC as the default timezone:
In your favorite text editor, open /etc/mysql/my.cnf
shell> vim /etc/mysql/my.cnf
Add this line in the ‘[mysqld]’ section:

And that’s all there is to it. Enjoy your new daylight-savings-immune database!

Related Tags: [ , , ]

Leave a Reply