Handling Temporal Data

A database needs to effectively and efficiently handle the storage, retrieval, and manipulation of the following types of temporal data:

• Dates

• Times

• Date and time intervals

• Time zones

Oracle’s support for temporal data is mature and efficient. Oracle8i supports convenient manipulation of date and time data. Oracle9i enhanced this support by introducing a new set of features including the support for fractional seconds, date and time intervals, and time zones.

Internal DATE Storage Format

Oracle’s DATE datatype holds date as well as time information. Regardless of the date format we use, Oracle stores dates internally in one standard format. Internal to the database a date is a fixed-length, seven-byte field. The seven bytes represent the following pieces of information:

1. The Century

2. The Year

3. The Month

4. The Day

5. The Hour

6. The Minute

7. The Second

Even though the datatype is called a DATE, it also stores the time. We choose the components to display (the date, the time, the date and the time, etc.) when we retrieve a DATE value from the database. Or, if we are fetching a DATE value into a program (e.g., a Java program) we might choose to extract the date ele- ments of interest after transferring the entire date/time value to that program.

Oracle provides two extremely useful functions to convert dates:

• TO_DATE

• TO_CHAR

As their names suggest, TO_DATE is used to convert character data, or numeric data, into a DATE value, and TO_CHAR is used to convert a DATE value into a string of characters.