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?
TIMESTAMP is the same as DATE, except it has added fractional seconds precision.
DATE is accurate to the second and doesn't have fractional seconds. TIMESTAMP has fractional seconds. The number of decimal places in the seconds depends on the server OS, for example the Oracle on my Windows 7 machine returns three decimal places for the timestamp whereas a client's huge Solaris box returns six. Timestamps can also hold a specific time zone or be normalized to common time zone - go here and then search for "TIMESTAMP" for more information, then experiment a little :)
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.
https://i.stack.imgur.com/yOVlg.png
https://i.stack.imgur.com/0XHtB.png
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- Difference between BYTE and CHAR in column datatypes
- What is the difference between Views and Materialized Views in Oracle?
- Get list of all tables in Oracle?
- How do I limit the number of rows returned by an Oracle query after ordering?
- Difference between a user and a schema in Oracle?
- Calculate difference between 2 date / times in Oracle SQL
- How to connect to Oracle using Service Name instead of SID
- What is the difference between precision and scale?
- Difference between CLOB and BLOB from DB2 and Oracle Perspective?
TIMESTAMP WITH TIME ZONE. Otherwise daylight saving time will introduce ambiguous times.TIMESTAMPwas added about 20 years afterDATE. They couldn't really go back and changeDATE.