Apply average( ) function to datediff between timestamp fields

Question

I have some data that looks like this:

my_table

name	start	end
Michigan	06-NOV-20 08.25.59.000000000 AM	06-NOV-20 08.44.52.000000000 AM
State	22-NOV-20 11.49.11.000000000 AM	22-NOV-20 11.54.06.000000000 AM

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:

duration
-0 0:18:53.0
-0 0:4:55.0

What I want:

duration
1133
295

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?

Please include the exact output you expect based on this 2-row sample input. — Tim Biegeleisen, Nov 14 '21 at 08:24
@dataviolet What do you mean by "I then want to apply a mathematical average (mean/median) to find the duration by year."? In your data there's nothing to take a meaningful arithmetic average of, nor does it relate to full-years. Please clarify. — Dai, Nov 14 '21 at 08:42
i'm trying to do an average of the duration by year. so in the sample above, I would ultimately calculate 714seconds as average duration in 2020. the biggest issue right now is my current output is in a timestamp format, but i think i need it in seconds format, in order to be able to apply an average in sql. — dataviolet, Nov 14 '21 at 08:43
@TimBiegeleisen - You were wrong to close this thread. The question isn't about computing the difference of two timestamps; the OP seems able to do that. The problem is that the result of taking the difference of two timestamps (as opposed to "dates") is an interval day to second, and alas (and for no good reason) Oracle did not implement aggregate functions for the "interval day to second" data type. The question is more subtle than you probably assumed, and it is definitely not answered in the thread you linked to. Or, if you think it is, it would help the OP (and others) if you showed how. — , Nov 14 '21 at 15:40

score 0 · Answer 1 · answered Nov 15 '21 at 09:16

0

I found this online. Will this help?

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

answered Nov 15 '21 at 09:16

Beefstu

804
6
11

score 0 · Accepted Answer · answered Nov 15 '21 at 20:48

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
     );

Apply average( ) function to datediff between timestamp fields

2 Answers2