Menu
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?
982
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
Just as DATETIME , the TIMESTAMP data type contains both the date and the time in the following format YYYY-MM-DD hh:mm:ss . However, unlike DATETIME , the TIMESTAMP data type has a fixed range between 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.
Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function.
Oracle TIMESTAMP literals To specify TIMESTAMP literals, you use the following format: TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF' The following example illustrates a TIMESTAMP literal: TIMESTAMP '1999-12-31 23:59:59.10'
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.
52
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
