I have some data that looks like this:

my_table

I'm trying to create a new column to calculate the duration as the difference between start and end. I then want to apply a mathematical average (mean/median) to find the average duration by year.

My code, currently:

SELECT
    start - end AS duration
FROM
    my_table

Current output:

What I want:

How can I go about converting the duration field from datetime to seconds or minutes, so that I can apply an average function to the duration field?

CodePudding user response：

I found this online. Will this help?

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:10729709873260

CodePudding user response：

I suggest you spend some time with the Oracle Documentation for your version, also for a fairly quick overview of date/timestamp see here (not just the first sub-topic).
Your issue is what is the results of date/timestamp subtraction. There are two results you can get. Subtracting dates results in a floating point number where the whole number part represents days, and the decimal part the fraction of a day. Subtracting timestamps results in an data type Interval Day to Second. This case deals with timestamps. The Extract is used to get the individual components of the interval. Since you are after the duration in seconds you extract each component multiplying by the appropriate value to convert to seconds, and total the results:

select  extract(day from diff) * 60 * 60 * 24     -- days to seconds
        extract(hour from diff) * 60 * 60         -- hours to seconds
        extract(minute from diff) * 60            -- minutes to seconds
        extract(second from diff)                 -- seconds
from ( 
      select to_timestamp('06-NOV-20 08.44.52.000000000 AM', 'dd-mon-yy hh12.mi.ss.ff AM')
           - to_timestamp('06-NOV-20 08.25.59.000000000 AM', 'dd-mon-yy hh12.mi.ss.ff AM') diff
        from dual
     );