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).

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).

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 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).

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.

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.

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.

DB2 and Oracle provide rich sets of facilities for manipulating date and time data types.Both provide native support for these data types, which eliminates the need to transform them into other types in order to support arithmetic operations like addition and subtraction. As with storage, both differ in date arithmetic operations as well.

Date Arithmetic in DB2

DB2 provides support for three date arithmetic operations:

• Subtracting DATE, TIME, or TIMESTAMP value from corresponding types to yield DURATION type (e.g., two DATE values can be subtracted, yielding the interval between them as DATE DURATION.)
• Addition and subtraction of similar DURATIONS from DATE, TIME, and TIMESTAMP (e.g., time duration can be added or subtracted from TIME or TIMESTAMP values only.)
• Addition and subtraction of labeled durations (of compatible time) from DATE, TIME, and TIMESTAMP (e.g., labeled duration of MICROSECONDS can be added or subtracted from TIMESTAMP values only.)

DURATION values are available only during execution time and are represented as packed decimal digits. In contrast to DB2 date and time types, duration carries the sign information to represent positive and negative duration. For example, SELECT DATE ('2005-03-01') - DATE ('2005-02-01') FROM SYSIBM.SYSDUMMY1 gives 00000100 as duration, which means a difference of one month between the two date values.

In DB2, date arithmetic is similar to numerical arithmetic using packed decimal digits. In the above example, DB2 would subtract the packed decimal representation of operand 1 from the packed decimal representation of operand 2. (Click here for more information on SYSIBM.SYSDUMMY1.)

Be cautious when you add or subtract two durations as it may lead to obvious wrong results. For example, if you add a duration of 1 day to a duration of 99 days, DB2 will return 1 month as the duration instead of 100 days?because DB2 does a packed decimal addition rather than a date addition.

Suppose you need to find the duration between two date or timestamp values such as days, months, or years. In such cases, first convert each operand into days or months or years using DB2 functions like DAYS or MONTH or YEAR, and then perform the arithmetic operation. For example, SELECT DAYS (DATE ('2005-03-01')) - DAYS (DATE ('2005-02-01')) FROM SYSIBM.SYSDUMMY1 returns the duration as days between two dates. SELECT MONTH (DATE ('2005-03-01')) - MONTH (DATE ('2005-02-01')) FROM SYSIBM.SYSDUMMY1 returns the duration as number of months between two dates. SELECT YEAR(DATE('2005-03-01')) - YEAR(DATE('2005-02-01')) FROM SYSIBM.SYSDUMMY1 returns the duration as number of years between two dates.

To add a day, month, or year to a date or timestamp value, you can use labeled types provided by DB2. For example, SELECT DATE('2005-04-01') + 10 DAYS FROM SYSIBM.SYSDUMMY1 adds 10 days to the given date. SELECT DATE('2005-04-01') + 2 MONTHS FROM SYSIBM.SYSDUMMY1 adds two months to the given date. SELECT DATE('2005-04-01') + 1 YEAR FROM SYSIBM.SYSDUMMY1 adds a year to the given date value. Labeled types are easy to use, they are self-documenting, and they make the code more readable.

Date Arithmetic in Oracle

Like DB2, Oracle can perform three date arithmetic operations:

• Subtract two DATE values, yielding the interval between the two as days
• Add an interval to a DATE or TIMESTAMP value (For example, adding six months to the current date yields a DATE value of six months past the current date.)
• Add or subtract two interval values, yielding an INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type.

In contrast to DB2, which returns the interval between two DATE values as a duration type, Oracle returns the interval between two date values as days. For example, SELECT TO_DATE('01-MAR-04') - TO_DATE('01-FEB-04') from DUAL returns 29 days rather than 0000 Years, 01 months, and 00 days as it would in DB2. (Click here for more information on the DUAL table in Oracle.)

Oracle doesn’t have functions for DAYS, MONTH, and YEAR like DB2 does. In Oracle, the intervals between two DATE and TIMESTAMP values are always returned as days. To get the interval in months, it provides a built-in function called MONTHS_BETWEEN. For example, SELECT MONTHS_BETWEEN (TO_DATE('01-DEC-04'),TO_DATE('01-FEB-04') ) FROM DUAL returns 10 months. If the second argument is later than the first, the result will be a negative value.

Similar to labeled types in DB2, if you want to add ‘X’ months to the current date or timestamp, first convert ‘X’ into an INTERVAL YEAR TO MONTH data type or INTERVAL DAY TO SECOND data type, and then add the intervals to date as in the following example:

SELECT TO_DATE('01-MAR-04')  + NUMTOYMINTERVAL(1,'MONTH')  FROM DUALSELECT TO_DATE('01-MAR-04')  + NUMTOYMINTERVAL(1,'YEAR')  FROM DUALSELECT TO_DATE('01-MAR-04')  + NUMTODSINTERVAL(2,'HOUR')  FROM DUAL

Instead of using the built-in functions to convert a numeric value to an interval type, you can declare an interval type explicitly as INTERVAL ‘X’ DAY or INTERVAL ‘X’ MONTH. For example, SELECT SYSDATE + INTERVAL '25' DAY FROM DUAL adds 25 days to the current date. Similarly SELECT SYSDATE + INTERVAL '2' MONTH FROM DUAL adds two months to the current date.

Why You Need to Know

You have seen the basic differences between date and time data types in DB2 and Oracle. A clear understanding of these differences will equip you to overcome most of the challenges related to migrating data from one database system to another?especially those related to chronological information. However, do not overlook other differences between the two, like formatting, time zone information, etc., which are also critical in real world scenarios.