Ed's Blog

MySQL, Dates and Time zones, Postgres and Mindshare

A database can’t be all things to everyone. Each one has its sweet spot. MySQL happens to be the Wal-Mart of the database world. A large market sweet spot with lots of really cheap stuff, questionably good for you and serving a basic need.

Occasionally, MySQL will let you down with some basic things… like time zones.

When you store a date/time, it's often (but not always) important to store the time zone information that came with it. If you think about it, it's not just useful to know it's 1:00pm when you're trying to calculate temporal differences between two dates. You need to know what time zone the date was for. You can just convert the date/time to the local time but then you lose the time zone that was associated with the date originally. A piece of information forever lost! If you always have a time zone associated with a date/time, then importing and calculating dates across multiple time zones is incredibly simple. If not, you have to make assumptions about the time zones that can be confusing and very error prone.

But? Does MySQL blend? Dates and time zones that is.

MySQL doesn't handle time zones correctly. Not to worry, 3 and half years ago we were promised it was in the plans.

[22 Nov 2004 12:05] Sergei Golubchik Yes, we plan to add standard TIMESTAMP WITH TIME ZONE type soon

Yet, MySQL 6.0 still does not support it.

MySQL seems to be Linux's answer to dBase files with InnoDB added as a retrofix to be ACID compliant. Now, don't get me wrong, dBase has its place. It's actually very fast (when implemented with FoxPro), but it does have its limitations with both scaling and concurrency, as does MySQL. MySQL has improved both with a great implementation of replication and with InnoDB. But it's still not ready for extremely large or complex systems. Oracle is. PostgreSQL is. MSSQL is getting better. Will MySQL get there?

A better question is will PostgreSQL get MySQL’s mindshare first and make it all not matter? Where MySQL is undoubtedly #1 is mindshare. With the further corporizatation of the product, will a truly open source database like PostgreSQL take over?

It's interesting to note that because of the background of some MySQL users, many don't even see the need for ACID compliance. Other's don't see the need for time zone support in date times. Any most have no need to build super complex system or have the head room to scale to support Skype type database sizes. MySQL is good enough. And isn’t that what matters anyway?