Package: pkg_interval

As noted in a previous post, interval data types are used for storing an “amount of time” without specifying exactly when that time is, e.g. 10 minutes, 1 day, etc. Also as noted previously, while first appearing in Oracle 9i there are areas of functionality lacking in Oracle’s native support for interval data types. The last post looked at SQL aggregate functions for intervals while this post will look at extracting the interval value as a single unit of measure.

Suppose you have a long running process where the start and end time is tracked and recorded in a log table in timestamp columns. Obtaining the duration of the process is a simple matter of subtracting the start time from the end time, resulting in an interval data type duration, i.e.:

DECLARE
   l_start_timestamp  TIMESTAMP;
   l_end_timestamp    TIMESTAMP;
   l_process_duration INTERVAL DAY TO SECOND;
BEGIN
   l_start_timestamp := TO_TIMESTAMP('08:00:00.00','hh24:mi:ss:ff'); -- in place for SYSTIMESTAMP;
   --<>
   l_end_timestamp   := TO_TIMESTAMP('09:32:51.37','hh24:mi:ss:ff'); -- in place for SYSTIMESTAMP;
   
   l_process_duration := l_end_timestamp - l_start_timestamp;
   
   dbms_output.put_line ('Duration: ' || TO_CHAR(l_process_duration));
END;
/

The output of the above is:

Duration: +00 01:32:51.370000

This isn’t too bad but it could be confusing if you didn’t understand that the format was day, hours, minutes, seconds and fractions of seconds. If we simply wanted to report the duration in terms of minutes then we’d have to try a different approach. Reading the SQL documentation you may think that the EXTRACT function would be exactly what’s required. If we change the output statement in our script to:

   dbms_output.put_line ('Duration: ' || TO_CHAR(EXTRACT(MINUTE FROM l_process_duration)));

we get an output of:

Duration: 32

So, the EXTRACT function has simply extracted the minutes component from the overall duration, not expressed the entire duration in minutes. Anyone familiar with the way Oracle handles DATEs will probably find this rather annoying. If we were to use DATE data types we could draw the difference between the start of the process and the end of the process, which Oracle converts to a “number of days” and multiply by 24 x 60 (24 hours in a day and 60 minutes in an hour):

DECLARE
   l_start_timestamp  TIMESTAMP;
   l_end_timestamp    TIMESTAMP;
   l_process_duration NUMBER;
BEGIN
   l_start_timestamp := TO_TIMESTAMP('08:00:00.00','hh24:mi:ss:ff'); -- in place for SYSTIMESTAMP;
   --<>
   l_end_timestamp   := TO_TIMESTAMP('09:32:51.37','hh24:mi:ss:ff'); -- in place for SYSTIMESTAMP;
   
   l_process_duration := CAST(l_end_timestamp AS DATE) - CAST(l_start_timestamp AS DATE);
   
   dbms_output.put_line ('Duration: ' || TO_CHAR(l_process_duration*24*60) || ' minutes');
END;
/

Here we are casting our TIMESTAMP variables to DATEs, drawing the difference and expressing that in minutes, which results in:

Duration: 92.85 minutes

Looks good right? Certainly the whole digits is correct but on close observation you’ll note that the decimal places is out by 0.01. The correct answer, to 2 decimal places, is 92.86. What’s happened here is in the CASTing of the TIMESTAMPs to DATEs Oracle has truncated the 0.37 fraction of a second (DATEs don’t support fractions of seconds), which resulted in it rounding the result differently. Okay, admittedly, you’re not likely to be worried about that in this scenario but it may be of concern in other situations.

So, what can we do to express our duration in minutes and still preserve the sub-second precision that the TIMESTAMP data type gives us? Well, looking back at the EXTRACT function, we can extract the various components from the duration, perform the necessary artimetic to convert to minutes, add them up and output that:

DECLARE
   l_start_timestamp  TIMESTAMP;
   l_end_timestamp    TIMESTAMP;
   l_process_duration INTERVAL DAY TO SECOND;
BEGIN
   l_start_timestamp := TO_TIMESTAMP('08:00:00.00','hh24:mi:ss:ff'); -- in place for SYSTIMESTAMP;
   --<>
   l_end_timestamp   := TO_TIMESTAMP('09:32:51.37','hh24:mi:ss:ff'); -- in place for SYSTIMESTAMP;
   
   l_process_duration := l_end_timestamp - l_start_timestamp;
   
   dbms_output.put_line ('Duration: ' || 
                         TO_CHAR((EXTRACT(DAY    FROM l_process_duration) * 24 * 60) +
                                 (EXTRACT(HOUR   FROM l_process_duration) * 60) +
                                 (EXTRACT(MINUTE FROM l_process_duration)) +
                                 (EXTRACT(SECOND FROM l_process_duration) / 60)) || ' minutes');
END;
/

which gives us:

Duration: 92.85616666666666666666666666666666666667 minutes

We could apply the ROUND function to round the result to a reasonable number of decimal places. However, you will note that the result is accurate. You’ll have also noted that the expression to do this is rather longwinded. This type of thing is ideal for wrapping up into a utility package; pkg_interval in my utility library.

pkg_interval contains conversion routines that take INTERVAL DAY TO SECOND data type parameters and express them in units of a single time measure. The name of the routine tells you what the unit of measure is, i.e.:

• to_seconds
• to_minutes
• to_hours
• to_days

Using the to_minutes function in our example above, the output statement would then be:

dbms_output.put_line ('Duration: ' || TO_CHAR(pkg_interval.to_seconds(l_process_duration) || ' seconds'));

which gives us the output of:

Duration: 92.85616666666666666666666666666666666667 minutes

As noted previously, the result can be ROUNDed to however many decimal places you deem necessary.

---

Download scripts shown in this post.

Advertisement