Monday, October 01, 2007

Issues with Oracle, Time and JDBC

1) java.sql.Date doesn't contain time information (hh:mm:ss) (However 'DATE' data type contains time information). If you want to include time also along with date, you need to use java.sql.Timestamp.
2) 'DATE' and 'TIMESTAMP' data types doesn't contain time zone information. If you want to include time zone, you need to use either 'TIMESTAMP WITH TIME ZONE' or 'TIMESTAMP WITH LOCAL TIME ZONE'.
2) If the time zone of JVM and time zone of session are different, the time will not be saved correctly into Oracle. It will pick time component from JVM and time zone component from session (without conversion). Hence you need to keep the session time zone as same as the JVM time zone.
3) There is no direct way to retrieve the time with time zone from 'TIMESTAMP WITH TIME ZONE'. You need to retrieve as string and parse it.
4) If you use 'TIMESTAMP WITH LOCAL TIME ZONE' you can retrieve the time with time zone correctly. However you need to set the time zone explicitly on the session. You need to use the Oracle specific API OracleConnection.setSessionTimeZone().
5)You cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns ( ORA-30079)

One possible solution would be keep Oracle, Application server and session are in GMT. Make time zone conversions at all input output points.

1 comment:

Anonymous said...

Best post on this subject on the whole internet! Thanks!