ost enterprise applications today use database systems to persist data, and they utilize these data for various organizational needs. Often these applications need to store, retrieve, and manipulate date and time values to perform various business processes. Commercial databases, such as IBM's DB2 Universal Database Ver. 8.2 (DB2) and Oracle 10g (Oracle), provide extensive native support for date and time data types, which reduces the load of manipulating these data types and increases performance. Being from different software houses, they provide different levels of support for these data types, as well as differing storage, retrieval, and manipulation functionalities.
This article provides an analysis of the basic differences between the date and time data types in DB2 and Oracle, focusing on DATE, TIME (available only in DB2), and TIMESTAMP in particular. It begins by introducing the data types in each database and moves on to cover their storage needs, internal representation, and values ranges. Finally, it discusses the differences in their date arithmetic operations. Understanding these differences can come in handy, especially when you are migrating data from one database to another.
Date and Time Types in DB2 and Oracle
DB2 provides three basic data types for working with date and time: DATE, TIME, and TIMESTAMP (see Table 1).
| Data Type |
Example |
Constituent Elements |
Description |
| DATE |
2005-01-01 |
Year, month, and day |
Represents January 1, 2005 |
| TIME |
13:00:00 |
Hour, minutes, and seconds |
Represents 1 PM |
| TIMESTAMP |
2005-01-01.12.00.00.000000 |
Year, month, day, hour, minute, second, and microseconds |
Represents January 1, 2005, 12:00 PM |
| Table 1. Date and Time Types in DB2 |
In DB2, TIMESTAMP is a combination of DATE and TIME values, with the time value having precision up to fractional microseconds (one-millionth of a second).
Oracle provides only two basic data types for working with date and time information: DATE and TIMESTAMP. (Oracle does offer TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE as well, but they are just variations of the TIMESTAMP type and are beyond the scope of this discussion.)
Oracle has no equivalent to DB2's TIME data type. Instead, it stores time with DATE values (see Table 2).
| Data Type |
Example |
Constituent Elements |
Description |
| DATE |
2005-01-01 12.00.00 |
Year, month, day, hour, minute, and seconds |
Represents January 1, 2005, 12:00 PM |
| TIMESTAMP |
2005-01-01.12.00.00.000000000 |
Year, month, day, hour, minutes, seconds, and nanoseconds |
Represents January 1, 2005, 12:00 PM |
| Table 2. Date and Time Types in Oracle |
In Oracle, the TIMESTAMP type is an extension of the DATE type, with the time value having precision up to nanoseconds (one-billionth of a second).
MIN and MAX Values
Table 3 presents the minimum and maximum values for DATE, TIME, and TIMESTAMP in DB2 and Oracle.
| DB2 |
| Data Type |
Minimum Value |
Maximum Value |
| DATE |
0001-01-01 |
9999-12-31 |
| TIME |
00.00.00 |
24.00.00 |
| TIMESTAMP |
0001-01-01.01.00.00.00.000000 |
9999-12-31.24.00.00.000000 |
| Oracle |
| Data Type |
Minimum Value (excluding Year 0) |
Maximum Value (excluding Year 0) |
| DATE |
- 4712-01-01.00.00.00 |
9999-12-31 |
| TIMESTAMP |
- 4712-01-01.00.00.00.000000000 |
9999-12-31.23.59.59.999999999 |
| Table 3. Minimum and Maximum Values for DB2 and Oracle |
DB2 and Oracle differ in the minimum values they can store in date and time data types. DB2 stores DATE values starting from Common Era (CE) whereas Oracle starts from year -4712, which is Before Common Era (BCE).
