All geek questions in one place

Insert UTC / GMT Date in Oracle Database with Java and Spring

When I insert a new Date() object using jdbcTemplate into an Oracle database, I see that the jdbc or Spring jdbcTemplate insert Date driver uses the 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 a Date object created in GMT, this result inserts 08-SEP-1987 into the Oracle database if the JVM time zone is USA.

+4
source share
2 answers

Neither java.util.Date nor Oracle Date saves time zone information. In your case, the Jdbc driver will convert your date using the JVM time zone. You can use one of the following options:

  • If you use PreparedStatement, you can use the setDate(int parameterIndex, Date x, Calendar cal) method to specify Calendar in the UTC time zone.
  • For Spring jdbcTemplate instead of pasting a Date object, insert Calendar with UTC timezone
  • TimeZone.setDefault(TimeZone.getTimeZone("GMT")) can be set on the JVM lvl
  • Use -Duser.timezone=GMT when starting the JVM
+18
source

Oracle DATE datatype does not have a timezone field. Only date and time components are stored in it. Therefore, when jdbc inserts a date with a time zone in the DATE database field, it must decide what to do with the time zone information that will disappear.

In your case, it seems that jdbc converts java DATE to the locale time zone before insertion. Date 09-SEP-1987 00:00:00 UTC converted to 08-SEP-1987 20:00:00 EST , and time zone information is discarded when inserted.

Knowing that you can either not specify the time zone when pasting into the DATE field to use the default time zone, or change both the default time zone and the java DATE time zone.

+3
source

Source: https://habr.com/ru/post/1435892/

More articles:


All Articles