Insert UTC/GMT date in Oracle database with Java a

问题:

When I insert new Date() object using jdbcTemplate to Oracle database, I can see that jdbc driver or Spring jdbcTemplate insert Date using local JVM offset.

SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
Date timeZoneDate = sdf.parse("09-SEP-1987");

For example when I insert Date object created in GMT this result to inserting 08-SEP-1987 in Oracle database if JVM timezone is USA.

回答1:

Neither java.util.Date nor Oracle Date stores timezone information. In your case Jdbc driver converts your date using the JVM timezone. You can use one of the following options:

• If you are using PreparedStatement, you can use setDate(int parameterIndex, Date x, Calendar cal) method to specify Calendar in UTC timezone.
• For Spring jdbcTemplate instead of inserting Date object, insert Calendar with UTC timezone
• TimeZone.setDefault(TimeZone.getTimeZone("GMT")) could be set on JVM lvl
• Use -Duser.timezone=GMT on JVM startup

回答2:

The Oracle DATE datatype doesn't have a timezone field. It stores only the date and time components. Therefore when jdbc inserts a date with a timezone into a DATE database field, it has to decide what to do with the timezone information that will disappear.

In your case, it seems that jdbc converts the java Date to the locale time zone before inserting. The date 09-SEP-1987 00:00:00 UTC is converted to 08-SEP-1987 20:00:00 EST and the timezone information is dropped on insert.

Knowing that, you can either not specify a timezone when inserting into a DATE field so that the default locale time zone will be used or modify both the default time zone and the java Date timezone.