Does JDBC adjust the Date before it inserts it into Oracle DB? How do I prevent this?

Question

Say we have the following code that creates date:

SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" );
sdf.setTimeZone( TimeZone.getTimeZone( "UTC" ) ); // we know the date being parsed is UTC
Date bizDate = sdf.parse( "12/12/2015" ); // milliseconds: 1449878400000
log.info( "ms: {}", bizDate.getTime() );
log.info( "date: {}", bizDate );
... // save to db

If that code runs on a JVM in UTC on an Oracle DB in UTC, I'm getting:

JVM params: -Duser.timezone=UTC

millisecond: 1449878400000
date: Sat Dec 12 00:00:00 UTC 2015
in oracle db: 2015-Dec-12 00:00:00 // zero time

For JVM not set to UTC (e.g. SGT) I'm getting:

JVM params: none (default timezone is SGT or UTC+8:00)

millisecond: 1449878400000
date: Sat Dec 12 08:00:00 SGT 2015
in oracle db: 2015-Dec-12 08:00:00 // plus 8 hours

Notice that they both have the same milliseconds but they were inserted differently in the DB.

My questions are:

1. Does JDBC standard say it adjusts the Date objects before inserting it? Please cite your source.

2. If JDBC does really adjust the Date objects before inserting it into the DB when your JVM's timezone is not set to UTC, why was it designed that way? I feel like that's making it more confusing. I was expecting it will insert it as it is. Imagine if you create a date using milliseconds (e.g. new Date( 1449878400000L ) ) and it will be stored differently and you have no information about the JVM's timezone your code will be running in. Or imagine your code will be running on multiple JVMs set to different timezones.

3. How do I prevent JDBC from adjusting the date when JVM's timezone is set to anything other than UTC? I'm using ibatis and I may not have direct access to PreparedStatements.

I have set the SimpleDateFormat's timezone to UTC because I want to treat the date being parsed as UTC (or as other timezone as required). It would have not been a problem had there been no such requirement. Now it seems I need to adjust the Date to reverse what JDBC is doing before inserting it.

Thorlay

Answer 1

The problem is that Java Date objects don't store a time zone. The value is always in UTC, and is parsed and formatted in a given time zone, usually the JVM's default time zone.

Oracle DATE columns are also stored without time zone, but should represent the date as seen by the user. In 99.99% of cases, that means the date in the JVM's default time zone.

So, the JDBC driver takes the Timestamp / Date value, which is in UTC, converts it to the default time zone, and saves that to the database.

You are using the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) method. To control the time zone, use the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) method. Quoting the javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

Piloting

I know that Oracle DATE has no timezones. Timezones are just presentation metadata. Dates may have different timezones but their underlying milliseconds are just the same. The question is why JDBC driver needs to adjust it to local timezone which effectively changing its underlying milliseconds when saved in the DB? – Thorlay

Or to put it another way, can I ask the driver not to "calculate the timestamp" because I want to store the milliseconds as it is because I know what I'm doing? – Thorlay

Because DATE in Oracle are not stored in UTC, but in the database time zone, so the UTC value must be converted. As long as the JVM time zone and the database time zone are the same, there is no problem. Please read this for more on Oracle time zone support: docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm – Piloting

Yes, use the 3 parameter version like I said, and specify the time zone in the Calendar object. In your case, that would be the UTC time zone. And you're obviously not knowing what you are doing, or you wouldn't have these problems. – Piloting

In my case, the Oracle DB's timezone is set to UTC and I'm getting problems when JVM's timezone is different from the DB's. The code will run on multiple JVMs in different timezones. – Thorlay

You are likely handling time zone mapping in the wrong place. There are many time zone settings involved: The DB SERVER time zone, the DATABASE time zone, the SESSION time zone, the APP SERVER time zone, the JVM time zone, the time zone given if using the Calendar version, and the USER time zone (e.g. web browser). Beware how they all interact. – Piloting

select DBTIMEZONE, SESSIONTIMEZONE from dual gives me UTC, UTC. Not sure if DB Server timezone matters, and if it does, I don't know how to get it. App servers' timezones will be different depending on the data center location. – Thorlay