Oracle sqlSubtract between two date

Question

I need help to write query do the following: subtract between two columns (start date and end date), and please note that the type for the columns are char not date, this is the exact format: 10-MAR-12 11.11.40.288389000 AM), then get the average for the result.

Answer 1

I assume this is homework, so some hints...

First don't ever store dates as varchar, it will cause you and the optimiser all sorts of problems.

Second, Oracle's date datatype can only store to second precision, and your string has fractions of a second, so you are looking at timestamp rather than date. You can convert your string to a timestamp with the to_timestamp() function, passing a suitable format mask. Oh OK, I'm feeling generous:

select to_timestamp(start_date, 'DD-Mon-RR HH.MI.SS.FF9 AM') from your_table;

Third, subtracting two timestamps will give you an interval data type, from which you will need to extract the information you want in a readable format. Search this site or elsewhere for timestamp subtraction, but I'll point you at this recent one as a sample.

The average is a bit trickier, so you may want to convert your intervals to numbers for that; again search for previous questions, such as this one. The size of the intervals, the precision you actually care about, and the way you want the output formatted, etc. will have some bearing on the approach you want to take.

---

If you need an approximate result then @Joachim Isaksson's answer will give you that - 'approximate' because of rounding; a duration of less than a second will show up as zero, for example. The same effect can be seen with timestamps cast to dates, which also loses the fractional seconds:

select 24*60*60*avg(
    cast(to_timestamp(step_ending_time, 'DD-Mon-RR HH.MI.SS.FF9 AM') as date)
        - cast(to_timestamp(step_starting_time, 'DD-Mon-RR HH.MI.SS.FF9 AM') as date)
    ) as avg_duration
from process_audit;

A more accurate answer can be found by extracting the various components of the timestamps, as in a question I linked to earlier. You may not need them all if you know that your durations are always less then an hour, say, but if you need more than one (i.e. if a duration could be more than a minute) then using an intermediate common table expression simplifies things a bit:

with cte as (
    select to_timestamp(step_ending_time, 'DD-Mon-RR HH.MI.SS.FF9 AM')
        - to_timestamp(step_starting_time, 'DD-Mon-RR HH.MI.SS.FF9 AM') as duration
    from process_audit
)
select avg(extract(second from duration)
    + extract(minute from duration) * 60
    + extract(hour from duration) * 60 * 60
    + extract(day from duration) * 60 * 60 * 24) as avg_duration
from cte;

With two sample rows, one with a gap of exactly a second and one with exactly 1.5 seconds, this gives the result 1.25.