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

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.

Previous Page

Next Page