Storing dates/timestamps in the database

One principle I like to follow is to have the database stand on its own as much as possible. So, if you use the standard date or even timestamp in Oracle at least, you have no means to tell what the time zone for it.

One would think that if you used the Oracle Date with Timestamp datatype, it would resolve your problem. However, jdbc (unless I missed something) does not recognize the timezone in this field. It is simply ignored. And, all times are assumed to be in the time zone of the JVM.

At least in one project I was involved with, I saw code where the programmer had misunderstood how it all works and introduced a bug where the time when it was read in was two hours earlier than the time that was written out.

If you have multiple app servers in different time zones updating the database, this would be a real issue. Ideally of course, your code/database should be independent of the timezone of the JVM.

As you can imagine, archiving the data or feeding it to secondary systems would be more problematic if one had to guess at or otherwise determine the timezone of the field.

I have two solutions to this problem. One is a little more cumbersome than the other -- but a little more database browsing friendly.

First you need to understand java.util.Calendar. My understanding of it is that the core value of this object is the time in milliseconds since the epoch in GMT. This is a cardinal value -- it is completely unaffected by the time zone of the JVM etc.

And java.util.Calendar is very sophisticated. It knows all the time zones in the world, but even more impressively knows whether or when daylight savings is observed in that time zone, further -- it also knows if and when the dates of the switchovers were changed.

So, once you have the cardinal value from the database, you can create a new java.util.Calendar object, setTimeInMillis(thatValue), and you are in business. Say you want to display the time in Central. You set the timezone of the Calendar object by something like setTimeZone(new TimeZone("America/Chicago")); and now you can display it using either SimpleDateFormat or FastDateFormat, using a formatter like "MM/dd/yyyy HH:mm:ss Z" -- and can rest assured that the correct time zone for that specific date will be displayed -- CDT or CST.

In summary:

Store the time in the database as a long integer -- from java.util.Calendar.getTimeInMillis(). Then when you read it back:

Calendar cal = Calendar.createInstance();
cal.setTimeInMillis(rs.getLong("fieldName");

Now, if and when you want to display this -- I think you should always show the timezone -- and you should pick one and not let it default to the timezone of the JVM.

so, then

TimeZone tz = TimeZone.getTimeZone("America/Chicago");
cal.setTimeZone(tz);
org.apache.commons.lang.time.FastDateFormat fdf = FastDateFormat.getInstance("MM/dd/yyyy hh:mm a z",Locale.US);
String displayValue = fdf.format(cal);

One drawback of this scheme is that you don't know the timezone of the original timezone. If you needed to know it for some reason, you could store it in a separate field. I recommend a GMT-HH:MM format, as not all timezones translate easily to something like "America/Chicago";

The second option is a little more complex. You can use the Oracle DATETIME with TIMEZONE data type. But now you cannot use the standard java.sql.PreparedStatement.setDate(java.sql.Date) or java.sql.ResultSet.getDate() functions.

Instead you would need to do something like this:

Update tablename set fieldName = TO_TIMESTAMP(?,"YYYY-MM-DD HH24:MI:SS.FF TZR") where primaryKeyFieldName = ?

you'd prepare a statement with the above string then:
FastDateFormat fdf = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss:SSS Z");
ps.setString(1, fdf.format(cal));
ps.setString(2, keyValue);
ps.executeUpdate();

When reading it, you'd do the reverse:

select to_char(fieldname,"YYYY-MM-DD HH24:MI:SS.FF TZR") from tableName where keyFieldName = keyValue;

then String charValue = rs.getString(1);
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS Z");
java.util.Date date = sdf.parse(charValue);
cal.setTime(date);

The nice part of this technique is that the database contains the relevant information in one field and it is easy to read.

This is not meant to be working code--I've typed this mostly from memory. But should give you enough information to use the technique in your code. Note that SimpleDateFormat.parse() is not threadsafe. It would need further refinement if i18n.