Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Dates in DB2 and Oracle: Same Data Type, Different Behavior : Page 2

Date, time, and their variants differ more than any other data types when you compare DB2 and Oracle. Find out more about these differences and why they're important to know.


advertisement
Oracle can store TIMESTAMP values with a variable precision ranging from seconds to nanoseconds. If you don't specify the needed precision while declaring the TIMESTAMP type, the Oracle database manager will default to microseconds.

DB2 supports precision up to microseconds under the TIMESTAMP data type. However, unlike Oracle, the precision in DB2 is not variable.

Internal Representation

Internal representation defines how database systems store each data type and its storage needs. DB2 and Oracle differ in how they store date and time data types.



In DB2, DATE values need four bytes for storage. Each byte carries two digits as packed decimal digits. The first two bytes store the year, the third byte stores the month, and the fourth byte stores the day. (A packed decimal digit carries sign information in its last nibble (half byte). But DB2 won't store sign information in the last nibble with DATE, TIME, and TIMESTAMP values.)

DB2 stores TIME values in a 24-hour clock format and needs three bytes for storage. The first byte stores the hour, the second stores the minute, and the last stores the seconds—each of them as packed decimal digits.

The TIMESTAMP data type is a combination of date, time, and milliseconds, and it needs ten bytes for storage. The first four bytes carry date, the next three bytes carry time, and the last three bytes carry the microseconds as packed decimal digits.

Table 4 presents the internal representation of the TIMESTAMP value 01/01/2005 11:30:30.000000 in DB2.

Year Month Day Hour Minute Second Milliseconds
Byte 1 2 3 4 5 6 7 8 9 10
Value 2005 01 01 11 30 30 000000
Note: In disk, the values are stored as binary. To make them easier to understand, the table represents the actual values rather than the binary representations.
Table 4. Minimum and Maximum Values for DB2 and Oracle

The first four bytes represent the DATE part, the next three represent the TIME part, and the last three represent microseconds.

In Oracle, DATE values need seven bytes for storage. The storage needs of TIMESTAMP values vary between seven and 11 bytes (variable length data type). For DATE, the first byte stores century, the second stores year, the third stores month, the fourth stores day, the fifth stores hour, the sixth stores minutes, and the seventh stores seconds. For TIMESTAMP values, the first seven bytes are similar to those of the DATE value, and the last four bytes represent the fractional second's value—which is up to a precision of nanoseconds. However, if you store a DATE value in a TIMESTAMP column, it needs only seven bytes for storage, like a DATE value.

Table 5 presents the internal representation of the TIMESTAMP value 01/01/2005 11:30:30.9999999999 in Oracle.

Century Year Month Day Hour Minute Seconds Nanoseconds
Byte 1 2 3 4 5 6 7 8 9 10 11
Value 120 105 1 1 12 31 31 999999999
Note: In disk, the values are stored as binary. To make them easier to understand, the table represents the actual values rather than the binary representations.
Table 5. Internal Representation of TIMESTAMP in Oracle

You're probably pondering a couple of obvious questions about the internal representation of DATE values in Oracle:

  1. Why are the century and year values off by 100 rather than 20 and 5, respectively?
  2. Why are the hour, minutes, and seconds values off by 1?

When Oracle stores DATE and TIMESTAMP values, it provides an offset value of 100 for century and year. This offset helps Oracle to store DATE values from BCE. It adds an offset value of 100 to century and year for DATE values in CE, while subtracting century and year values from the offset value of 100 for DATE values in BCE.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap