All geek questions in one place

Java - Oracle - Insert Database - Timestamp to Date

I have a data column of type DATE. I am inserting through Java PreparedStatement, how can I save the timestamp when pasting? Can I save a timestamp in a DATE column?

I tried

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

But that did not work.

Thanks in advance.

+4
source share
3 answers

Mapping between SQL types and JDBC types in Oracle is a bit more complicated, especially for 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 DATE in the SQL standard. Until 9.2, when TIMESTAMP column support was introduced, Statement.setTimestamp will work for DATE columns. From 9.2 through 11.1, the JDBC driver mapped the SQL DATE type to JDBC DATE and SQL TIMESTAMP to JDBC TIMESTAMP . Possible solutions for storing timestamps using 10.2 JDBC drivers are listed in the Oracle JDBC FAQ and reproduced here:

There are several ways to solve this problem in 9.2-10.2 drivers:

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

  • Modify the application to use defineColumnType to define columns as TIMESTAMP, not DATE. There are problems with this because you really don't want to use defineColumnType if you don't need to (see What is defineColumnType and when should I use it?).

  • Modify the application to use getTimestamp rather than getObject. This is a good solution whenever possible, however many applications contain common code that relies on getObject, so this is not always possible.

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

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

You can also use the JDBC 11.1 drivers (they will work against the 10g instance), and the FAQ has the following:

Oracle JDBC 11.1 fixes this problem. Starting with this release, the driver maps to SQL DATE columns with java.sql.Timestamp by default. There is no need to install V8Compatible to get the correct display. V8Compatible is strongly discouraged. You should not use it at all. If you make it true, it will not hurt anything, but you must stop using it.

+8
source

I suggest the TIMESTAMP type, especially if we want to calculate the time difference between the two TIMESTAMP data types is much simpler than the DATE data type. For more details, please take a look at this post.

+1
source

Finally, it turned out that during storage we can not do anything. When choosing, we should use TO_CHAR.

thanks

0
source

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

More articles:


All Articles