Oracle's DATE does not have a time zone information, so you need to handle the time zones yourself. My advice would be to choose a time zone and store all DATE columns in the database in that time zone. GMT/UTC might be a good choice, but is it not necessary; you might choose a time zone closer to your local time zone, which might be more convenient to you. The time zone you choose should not have Daylight Saving Time, though, because in time zones that have DST it is not possible to represent all hours in the day when the DST ends. (GMT/UTC doesn't have Daylight Saving Time.)
The conversion can take place during storing/retrieving the dates from the DB. You'll use the
setDate/
getDate methods of
PreparedStatement or
Resultset which take an additional
Calendar parameter. The date is stored to the database in the time zone of the supplied calendar; upon retrieval it is converted to the time zone of the supplied calendar. I'm not sure these operations are thread-safe and I'd suggest allocating a new calendar for each
thread that uses these methods (I'm using
ThreadLocal variables for this myself).
Some of the methods I'm speaking about are:
java.sql.PreparedStatement#setDate(int, java.sql.Date, java.util.Calendar)
java.sql.ResultSet#getDate(int, java.util.Calendar)
Alternatively you might use a
TIMESTAMP WITH TIME ZONE, or
TIMESTAMP WITH LOCAL TIME ZONE as the data type, as these types take time zones into account and the time zone information should be transferred correctly between application and the database when using these data types. I don't have much experience with these, though.
