I have a table with a "TIMESTAMP(6) WITH TIME ZONE" column and I need to persist a value in a different time-zone as my JVM/local time-zone.
I tried the following (example) code:
@Test
public void testInsertTimestampTZ_JDBC() throws Exception {
final java.sql.Connection connection = getDatabaseConnection();
final Calendar cal = new GregorianCalendar(2013, 12 - 1, 15, 23, 55, 0);
cal.setTimeZone(TimeZone.getTimeZone("America/Bahia"));
final java.sql.PreparedStatement preparedInsert = connection.prepareStatement(
"INSERT INTO " + TEST_TABLE +
" (ID, TS_TZ, DESCRIPTION ) " +
" VALUES ("+ TEST_TABLE + "_PKS.nextval, ?, ?)",
new String[] {"ID"});
try {
preparedInsert.setTimestamp(1, new java.sql.Timestamp(cal.getTimeInMillis()), cal);
preparedInsert.setString(2, cal.toString());
preparedInsert.execute();
} finally {
preparedInsert.close();
}
}
As you see, I used the following method to pass the time zone to the database:
/**
* Sets the designated parameter to the given <code>java.sql.Timestamp</code> value,
* using the given <code>Calendar</code> object. The driver uses
* the <code>Calendar</code> object to construct an SQL <code>TIMESTAMP</code> value,
* which the driver then sends to the database. With a
* <code>Calendar</code> object, the driver can calculate the timestamp
* taking into account a custom timezone. If no
* <code>Calendar</code> object is specified, the driver uses the default
* timezone, which is that of the virtual machine running the application.
*
* @param parameterIndex the first parameter is 1, the second is 2, ...
* @param x the parameter value
* @param cal the <code>Calendar</code> object the driver will use
* to construct the timestamp
* @exception SQLException if parameterIndex does not correspond to a parameter
* marker in the SQL statement; if a database access error occurs or
* this method is called on a closed <code>PreparedStatement</code>
* @since 1.2
*/
void setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal)
throws SQLException;
Looking at the comment of the method, I expected that the time zone of the calendar is considered by the driver. But with SQLDevelopper I see, that my local time zone is used. (The result: 15.12.13 23:55:00,000000000 EUROPE/BERLIN).
The following code works, but has a direct dependency to the Oracle driver:
@Test
public void testInsertTimestampTZ_ORA() throws Exception {
final java.sql.Connection connection = getDatabaseConnection();
final Calendar cal = new GregorianCalendar(2013, 12 - 1, 15, 23, 55, 0);
cal.setTimeZone(TimeZone.getTimeZone("America/Bahia"));
final java.sql.PreparedStatement preparedInsert = connection.prepareStatement(
"INSERT INTO " + TEST_TABLE +
" (ID, TS_TZ, DESCRIPTION ) " +
" VALUES ("+ TEST_TABLE + "_PKS.nextval, ?, ?)",
new String[] {"ID"});
try {
Datum tstz =
new TIMESTAMPTZ(connection, new java.sql.Timestamp(cal.getTimeInMillis()), cal);
preparedInsert.setObject(1, tstz);
preparedInsert.setString(2, cal.toString());
preparedInsert.execute();
} finally {
preparedInsert.close();
}
}
(SQLDevelopper: 15.12.13 23:55:00,000000000 AMERICA/BAHIA)
Reading some other posts I think this issue is already known since a while:
https://community.oracle.com/message/3602642#3602642
http://palashray.com/2010/11/02/how-to-handle-oracle-timestamp-with-timezone-from-java/
But the only solution I found was to use the TIMESTAMPTZ (second example). Is there a possibility to save the time-zone using plain JDBC? What is the reason for this behaviour?
(I tested this with OJDBC 11.2.0.3.0 and OJDBC7 12.1.0.1 drivers)
Thanks for your help :-).