Why developers are never on time

If you have worked in IT as long as I do, you probably have noticed that developers have a tendency to not be on time. They are late for meetings, late for lunch, and on other days they are in at 6am or (and) they work until 3am the next morning. If you ever wondered why this is, I might have some answers for you.

You might also find this post interesting if you are trying to figure out why you can't get your time zones working in MySQL.

It says that converting a Date/Time to a Unix timestamp is lossy "due to conventions for local time zone changes". I am very curious as to what "conventions" MySQL is referring to in an attempt to hide their buggy time zone implementation.

The example in the MySQL documentation shows how the following two statements result in exactly the same Unix timestamp:

SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00'); SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00'); If you have worked with Dates and Times for international customers before, you will immediately notice that these two examples are lacking a crucial part, namely the time zone component. In these two cases, MySQL will "guess" the correct time zone for you. Both timestamps represent the crossover between winter- and summer time, depending on what time zone you put behind them (02:00 CEST = 03:00 CET). Apparently the MySQL algorithm for figuring out the "correct" time zone makes a pretty awful guess here.

To be able for the client to query the database directly, I needed to come up with a solution. I did some research and found out that the MySQL development team, in their infinite wisdom, chose to not install time zone support by default in MySQL. They also decided that any date/time function you use not so much as peeps about missing time zone support. I will not start a rant on how wrong either of those is. No I will not. Will. Not... Must... not......

Solving the MySQL time zone problem. When you connect to a MySQL instance, it is easy to check if the administrator has installed time zone support. Execute the following query:

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

If this query returns the very informative "null", then time zone support is not installed on your server. Go to the server (or ask your administrator to do this) and execute the following commands:

$ cd /usr/local/mysql/bin $ ./mysql_tzinfo_to_sql /usr/share/zoneinfo | \ mysql -u root mysql -p It will give you some useless warnings about unsupported time zones. They should have put that effort in warning us when we try to use time zones. (oops I did it again.. must... not...).

(Tested on MySQL 5.5 on Mac OSX as well as MySQL 5.5 on CentOS Linux)

If everything went smoothly, you should now be able to do a time zone conversion with the convert_tz() function. We need time zone support to be able to tell MySQL that our session will now use the GMT time zone. We choose this because Unix Time Stamps are (milli)seconds since 1970-1-1 00:00 GMT. Also, MySQL can not correctly tell the difference between CET and CEST so we steer clear of any summer/wintertime problems it has.

We can try our unix_timestamp conversion again:

set time_zone='GMT'; SELECT @@session.time_zone; SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00'); SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00'); The first statements sets the time zone, and the second shows your time zone setting. If this does not say "GMT" then either you made a typo or time zone support is not enabled but MySQL "forgot" to tell you (oh dear, doing it again, am I?). The last two statements should return different Unix Time Stamps, and you should be able to convert back from those into the correct readable (but missing time zone information) timestamps again.

So the whole problem comes down to MySQL "silently failing" when you use time zones it does not "understand", and the consistent lack of time zones in the date/time representations, both in the application and in the documentation.

It seems that a lot of developers on this world still have a hard time grasping that "yyyy-mm-dd HH:MM:ss" is never an exact representation of a point in time without a time zone component. Show me a date/time without a time zone and I show you software with time conversion bugs.

In our software, we do all date and time conversions ourselves, and store them as Unix Timestamps in in the database as a Long. This way no driver, client or server database will be able to do "smart" conversions for us. It helped us develop applications for customers in over 12 countries, spanning a multitude of time zones. The MySQL handling of time zones has reinforced my belief that this is the only way to have total control over dates and times.