MySQL/JDBC DateTime woes

The Situation

At my current company we store lots of timestamps; more specifically they are usually global timestamps. So clearly we needed to store the field as UTC. Why? Because we dozens of SQL servers which means we need a way to make sure that no matter where the servers are, the timestamps are always readable and understandable. Given this situation it has always been our idea to store the times in UTC. The biggest player in this is that JDBC attempts to convert timestamps for you so that you will always be in a local time. But what happens when you don't want to convert to a local time? For example, let's say you have two databases (for failover reasons) and you have two separate webapp servers that read from each of those databases (again for failover reasons except that the database primary is not bound to the web application primary). This means that you could have two sites one in India and one in Indiana and the India web app server reading from the Indiana database. So the conversion is never a constant thing. To add difficulty to this situation, let's assume that you have a group within your organization who has enough pull to state that these times must remain in UTC since that is what they are used to dealing with.

The Theory

Our solution starts by stating that we must keep the data in the database always be in UTC. Let's assume that the database servers are setup to be in UTC; so they are assumed to be UTC regardless. So let's digest the situation, we can look at this in 2 parts, the first is storing the timestamp in UTC. The second is retrieving the timestamp and converting it back into UTC from local (remember that JDBC is going to convert it to local for us).

How to store the timestamps in UTC?

The first part of this is knowing that there are two ways that we can get a timestamp to store. The first is to create a timestamp representing now. For this, we can just use a Calendar and store that. Simple enough, remember that it will be converted over so it'll be converted to now in UTC. The second is to create a timestamp based on input from a user. Here is where the difficulty lies, let's assume that our user wants to store "2012-02-02 00:00:00" but not based on a localtime instead they want it based on UTC. Well we can accomplish this two ways, both involve creating a SimpleDateFormat object and using it to parse the time. The first way is to append the "z" format symbol to get the timezone from the user. The key here is to just append the "UTC" string on parse.

SimpleDateFormat obj = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
Date date_in_utc = obj.parse(str + " UTC");

So what is the other option? Well just set the timezone for the formatter of course.

SimpleDateFormat obj = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
obj.setTimeZone(TimeZone.getTimeZone("UTC"));
Date date_in_utc = obj.parse(str);

So why would I go with either of these options? They both have their pluses and minuses. The first option really allows us configuration. Let's say that we normally do UTC but we want to allow the user to specify their own timezones. Here we have that ability. But it's slower since we're going to be compiling the SimpleDateFormat each time and appending the "UTC" string each time. The second option means that we can compile the SimpleDateFormat once with it's UTC timezone set and not have to do any string appends when we want to perform the conversion.

How to get the timestamps in UTC?

Remember, we said that we are always going to get the timestamp in UTC and JDBC is going to convert the timestamp to the local timezone. So since we know that the time is in local time; how are we going to deal with this? Well let's just look at the display. Let's assume that we're going to display the time in UTC. How do we do it? The same way as in the previous step, except instead of doing a parse we do a format().toString as shown below.

SimpleDateFormat obj = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
obj.setTimeZone(TimeZone.getTimeZone("UTC"));
String date_in_utc = obj.format(str).toString;