MySQL Timezone Support

Question

I've added time zone support on my MySQL Server 5.6, running on Windows 2008 Server. I've set the default-time-zone to GMT, which works fine. In addition I've followed the instructions at http://dev.mysql.com/downloads/timezones.html to get the Time zone description tables.

The timezone information is now stored under the mysql database.

My other database is using Innodb engine with foreign keys Any fields that need date and time are stored in TIMESTAMP format. In one of my tables called Company, I need to put a reference to their timezone.

I've two questions;

• Should I be asigning a timezone to a individual company, or to a city/state/region to which that company belongs to

• Ideally I'd like to link their timezone, where ever it is referenced from with a foreign. But how is that possible if the timezone ids etc are in the mysql database, which I don't want a normal mysql user having access to.

What are the best practices for dealing with this sort of issue.

Answer 1

In my opinion you shouldn't try to manage Timezone through the DB.

If you're using PHP with MySQL you can easily translate timezone. For dates or what ever: DON'T USE TIMEZONE ! Make use and advantage of timestamp:

http://fr.php.net/time: <?php time(); ?>

You should definitely watch : http://www.youtube.com/watch?v=-5wpm-gesOY

Answer 2

Should I be assigning a timezone to a individual company, or to a city/state/region to which that company belongs to?

This is very application dependent, but in general you should consider assigning a time zone to a specific location of a company. A company with multiple offices might have them in different time zones. But you should think through what this means for your particular use case.

It's usually not a good idea to try to assign a time zone to a region, because there are too many edge cases. There are many countries and states that have multiple time zones, and a few cities that do as well (although not as common).

Often, a time zone is assigned to an individual user, or to a particular event. It just depends on what your application needs.

Ideally I'd like to link their timezone, where ever it is referenced from with a foreign. But how is that possible if the timezone ids etc are in the mysql database, which I don't want a normal mysql user having access to.

MySQL uses the IANA/Olson time zone database. (See also the timezone tag wiki and wikipedia.)

Since .Net doesn't use this data, if you want to align things with your database you will need to use a library, such as Noda Time or TZ4Net. (I highly recommend Noda Time.)

Of course, that's only required if you actually need to be working with time zones in your database. Often, you will have no such requirement.

What are the best practices for dealing with this sort of issue.

There's a whole list of best practices here. However, what you should do is highly dependent on your actual requirements. There's not a one size fits all answer.

Quite often, the best approach is to store values in UTC in your database, and convert to and from a certain time zone for display. In that situation, you don't need time zone data in your db at all, and you can take your choice of using IANA time zones, or using the Windows time zones that come with .Net's TimeZoneInfo class.

An example of where you might need time zone support in your database is if you have some requirement like "I want a report that is grouped by day, where a day is defined local to a particular time zone. Oh, and I want each viewer of that report to have their own local view of the data that is adjusted to their idea of what a 'day' is."