Oracle PL/SQL Programming, 5th Edition

Date/Time

My globalization discussion thus far has been focused on strings. Date/time issues, however, can be every bit as troublesome when localizing an application. Users may be on the other side of the world from your database and web server, but they still require accurate information relating to their time zone, and the format of the date and time must be in a recognized structure.

Consider the following issues related to date/time:

• There are different time zones around the world.

• Daylight savings time exists for some regions, and not for others.

• Certain locales use different calendars.

• Date/time formatting is not consistent throughout the world.

Timestamp Datatypes

Until Oracle9i Database, working with dates and times was fairly straightforward. You had the DATE type and the TO_DATE function. The limited functionality of the DATE type made application development of global applications somewhat tedious, though. All time zone adjustments involved manual calculations. Sadly, if your application is to work with Oracle8i Database or earlier versions, I’m afraid you are still stuck with this as your only option.

Those of us working with Oracle9i Database and later, however, benefit greatly from the TIMESTAMP and INTERVAL datatypes discussed in detail in Chapter 10. If you have not read that chapter yet, I’ll provide a quick overview here, but I do recommend that you go back and read that chapter to obtain a thorough understanding of the topic.

Lets take a look at an example of the TIMESTAMP, ...