Difference between oracle DATE and TIMESTAMP

oracle jdbc oracle11g
supernova picture supernova · Oct 2, 2013 · Viewed 88.2k times · Source

What are the difference between Oracle DATE and TIMESTAMP type? Both have date and time component? Also what is corresponding type in Java for these date types?

Answer

Guillermo Luque picture Guillermo Luque · Oct 2, 2013

DATE and TIMESTAMP have the same size (7 bytes). Those bytes are used to store century, decade, year, month, day, hour, minute and seconds. But TIMESTAMP allows to store additional info such as fractional seconds (11 bytes) and fractional seconds with timezone (13 bytes).

TIMESTAMP was added as an ANSI compliant to Oracle. Before that, it had DATE only.

In general cases you should use DATE. But if precision in time is a requirement, use TIMESTAMP.

And about Java, the oracle.sql.DATE class from Oracle JDBC driver, provides conversions between the Oracle Date/Timestamp data type and Java classes java.sql.Date, java.sql.Time and java.sql.Timestamp.

Related questions

Unable to connect to oracle via Pentaho. 'oracle.jdbc.driver.OracleDriver' could not be found

Hi I've just installed Pentaho Data Integration v. 5.01 and I'm trying to connect to a database. However, I'm unable to test the connection as I get the error (pasted below). I have JDK 1.7 installed on the system as well, however …

Read More
SQLException: Protocol Violation in oracle

I am getting the "Protocol Violation". I have an application running on RedHat Linux.The database and the application are co-resident on the machine. Oracle version used: Oracle 11g R2 (11.2.0.3.0) JDBC Driver used: 12.1.0.1 Java used: jdk1.7.0.65 32-bit I have come …

Read More
Setting Oracle size of row fetches higher makes my app slower?

As detailed here and confirmed here, the default number of rows Oracle returns at time when querying for data over JDBC is 10. I am working on an app that to has to read and compare lots of data from our …

Read More