Aug 172011
 

Okay, after many hours of battling with MySQL and PostgreSQL, I’ve come to the conclusion that databases support for dates and times suck. PostgreSQL appears to do a much better job than MySQL, but overall they both leave a lot to be desired. The main

The main issues with both these databases is that the lack the ability to store values as UTC without any timezone information. That’s right, I don’t want the database to know anything about timezones. See, once the database starts to store timezone information or worse, store dates and times without timezone information but not in UTC, you get into the situation where everything melts down if the timezone of the operating system or the database server changes. You also get into really nasty issues when you migrate data across locations, for example by exporting data from a server in San Francisco and migrating it to a server in London.

If you think about it, databases are primarily used for storing and retrieving data. In 99.9999% of applications, the database is NOT responsible for displaying data. For this reason, the database should not know anything about timezones because these are purely for display purposes. The correct way to store date and time data is at UTC.

So, what’s my solution given the horrible date and time support in databases? Don’t use them and use bigint instead. Store everything using the number of milliseconds since epoch in UTC. Never ever do any timezone math with your dates and times. Handle everything in UTC and only do timezone manipulations just before you display the data to a user.

I pulled this off by changing all of my datetime and timestamp columns to be bigint instead. They now look like this:

Additionally, in my application code when I use JDBC I pull the data out as Longs like this:

Lastly, I wrote a Hibernate UserType that converts from longs to Joda DateTime instances and annotate my entities like this:

And that’s it. It works perfectly and I never have to worry about any of my data getting screwed up if someone changes the system timezone or migrates data from one server to another. Furthermore, I finally get full support for milliseconds on MySQL and I also can add support to Clean Speak for new databases easily.

  4 Responses to “Database date/time support sucks”

  1. Does this affect your hql in any way when you have date related fields in the where clause?

    Will the value passed in as parameter in the following hql statement be a date (assuming dateOfBirth is a Date) or a Long?

    select p from People p where p.dateOfBirth > ?

    • In your example, it is actually a Joda DateTime that we put into the Query. Since we have a hibernate UserType, that DateTime is converted to a long and the long is set into the PreparedStatement by Hibernate.

  2. Simply storing the number of millis since the epoch seems a universally good idea. At first. Consider the following scenario’s:

    – Future daylight time saving changes will mess with your future appointments,
    – Location dependent scenario’s (e.g. a wake-up call in the morning),
    – Detecting inconsistencies: e.g. warning for an appointment happening in another time-zone.
    – Adding/substracting time. The length of a day differs in different time-zones given an identical number of millis since the epoch.

    The time-zone is a simplified location indicator. Removing it might destroy semantic information. Then again, in many cases storing millis since epoch is perfectly acceptable. Alternatively, one might include longitude and latitude to connect all four dimensions.

    • @Edwin: It seems like most of what you are talking about is futures. Anytime you store UTC milliseconds in the future, you might have shifts that would impact the your instant. However, this is mostly a solved problem. Since all shifts are timezones, the math to convert a UTC milliseconds to a new timezone is simple. Therefore, when the timezones shift, you can apply that shift to all future instants. You just have to plan for the inevitable changes in timezones.

      For our needs, we don’t store future instants, which makes our lives much simpler.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">