Before version 9i, Oracle had no built-in way to record the passage of time. The DATE datatype records an individual moment in time; but to express a quantity of time (i.e., an interval), database designers had to convert the interval to raw seconds and use a NUMBER column to store it.
Although the NUMBER datatype represents time in seconds accurately, it makes time calculations difficult. For instance, 60 seconds to the minute, 60 minutes to the hour, 24 hours in a day—these are all awkward numbers to deal with in a number system based on decimals.
In Oracle 9i, the time interval datatypes INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND were added to comply with the SQL 99 standard, along with several other datatypes to deal more precisely with points in time. The TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE datatypes all express time to fractions of a second, and the last two account for changes in geographical location as well.
You can use the interval datatypes in both SQL and PL/SQL. They are specified the same way:
INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]
There are defaults for the precision values: two digits for the year and day, and six digits for fractions of seconds.
Interval literals are expressed by the word INTERVAL, followed by an expression in single quotes, and words that interpret the expression. YEAR TO MONTH interval literals use a hyphen (-) between the year and month. DAY TO SECOND interval literals use a space between the number of days and time. For example, this is a time interval of two years and six months:
INTERVAL '2-6' YEAR TO MONTH
This covers three days, 12 hours, 30 minutes, and 6.7 seconds:
INTERVAL '3 12:30:06.7' DAY TO SECOND(1)
Intervals can be positive or negative. They can be added to or subtracted from the various TIMESTAMP datatypes; the result is a new TIMESTAMP. They can be added or subtracted from each other as well, resulting in a new interval.
Listing A shows how you would create a table to record the start time and duration of an event, such as an experiment. Once data has been collected, the built-in SQL summary functions can be used to report total and average duration, without the necessity of converting to and from raw seconds.
Unfortunately, the TO_CHAR function doesn't contain any format models that map to the individual pieces of interval datatypes. Instead, you can use the new EXTRACT function to extract pieces and combine them. Here is the format:
EXTRACT(timepart FROM interval_expression)
Listing B shows an example of this technique. First, the number of days is extracted from the column experiment_duration, and the literal "Days" is concatenated to it. The same is done for the hours and minutes portions of the experiment's duration.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.