Mastering Oracle SQL

Chapter 6. Handling Temporal Data

“Time and tide wait for none,” goes the wise saying. As database developers, we may not deal with tide-related information every day, but we need to deal with time-related information every day. The hire date of an employee, your pay day, the rent or mortgage payment date, the time duration required for a financial investment to mature, and the start date and time of your new car insurance are all examples of temporal data that we deal with every single day.

The need for effective management of temporal information became critical at the turn of the century, when most of us had to devise ways to handle the two-digit year correctly as it increased from 99 to 00, and then to 01. In this age of global e-business, the concepts of time are even more involved than ever before, because businesses are carried out around the clock across time zone boundaries.

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