Mastering Oracle SQL, 2nd Edition

Chapter 6. Handling Temporal Data

As the old saying goes, “Time and tide wait for no man.” As database developers, we may not deal with tide-related information every day, but we deal with time-related information almost every single 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 business is 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 support for fractional seconds, date and time intervals, and time zones.

Time Zones

In the global economy, business is carried out across geographical boundaries and time zones. It is common for a customer in Los Angeles ...