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.
Daily Tech Insider Newsletter
Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.
Delivered Weekdays
