Java - Oracle - DB Insert - Timestamp to Date column

Question

I have a table column of data type DATE. I am inserting through Java PreparedStatement , how can I store timestamp also when inserting? Is it possible to store time stamp in DATE column?

I tried

ps.setTimestamp(index, new java.sql.Timestamp(date.getTime()));

But did not work.

Thanks in advance.

Answer 1

The mapping between SQL types and JDBC types in Oracle is a bit complicated, especially for the DATE and TIMESTAMP SQL types.

The Oracle database allows you to store timestamp information in DATE columns, which is quite contrary to the definition of the DATE in the SQL standard. Prior to 9.2 when the TIMESTAMP column support was introduced, Statement.setTimestamp would work for DATE columns. Since 9.2, and until 11.1, the JDBC driver mapped the SQL DATE type to the JDBC Date and the SQL TIMESTAMP type to the JDBC Timestamp. The possible solutions to storing timestamps using the 10.2 JDBC drivers are listed in the Oracle JDBC FAQ, and are reproduced here:

There are several ways to address this problem in the 9.2 through 10.2 drivers:

• Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.

• Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it? ).

• Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.

• Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

  java -Doracle.jdbc.V8Compatible="true" MyApp

You could also use the JDBC 11.1 drivers (they will work against a 10g instance), and the FAQ has this to state:

Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to java.sql.Timestamp by default. There is no need to set V8Compatible to get the correct mapping. V8Compatible is strongly deprecated. You should not use it at all. If you do set it to true it won't hurt anything, but you should stop using it.