Use UTC or TIMESTAMP WITH TIME ZONE

2015 September 24

If you do business in a time zone that uses daylight saving time and you want to store dates with a time component in an Oracle database you likely want to store them in UTC with the DATE or TIMESTAMP datatypes or use the TIMESTAMP WITH TIME ZONE datatype. You likely do not want to use the DATE or TIMESTAMP datatypes with a time zone other than UTC. You may think that you do not have to deal with time zones because you operate only in one time zone and therefore do not need to store the time zone. But in reality you are operating in two time zones: summer time and winter time. If you want to get the same values back from the database that you stored you have to store the time zone or accept silent data truncation.

Oracle stores the DATE and TIMESTAMP in local time without any time zone information.

For example European Summer Time ends at three o’clock on 25 October 2015. At three o’clock in the morning the clock jumps back to two o’clock. This meens the hour between two and three o’clock happens twice and you can not distinguish them without knowing the time zone.

As a concrete example these two timestamps

2015-10-25T02:45:00+02:00
2015-10-25T02:45:00+01:00

both get stored as DATE '2015-10-25 02:45:00' in the database. This means there is a whole hour (or how much your clock jumps) every year which you can not store in the database. Values that you do store silently get an hour subtracted.

Storing values in UTC avoids these issues because UTC has no daylight saving time. However using UTC is not without its downsides. You have to remember to convert to UTC when storing values and convert again when reading values. Also when viewing values directly with a tool like sqlplus you have to remember that the values displayed are in UTC and not local time (unless you live in Iceland).

Only since version 4.2 does JDBC support reading and writing time zones (by use of the ZonedDateTime and OffsetDateTime data types). Unfortunately the Oracle JDBC driver does not yet implement JDBC 4.2 (as of Oracle 12.1c). You are therefore forced to use TIMESTAMPTZ if you want to read and write dates with time zones in Java.