How to Calculate Average Daily Time from Start_Time and End_Time fields

user10903866

I am using ORACLE 11G on Red Hat Linux, I am trying to calculated the average Daily time from multiple transactions for each day. The followign is the querry which shows the time difference between the start_ time and end_time fields.

select TRIP_LOAD_STARTED AS START_TIME,TRIP_LOAD_ENDED AS END_TIME,

ceil(( TRIP_LOAD_EDDT - TRIP_LOAD_STDT) * 1440) " Time Difference Bw Start and End"
from GATE2GATE_ACTIVITY_VW
Where TRIP_LOAD_STARTED >= '01/FEB/14'
AND TRIP_LOAD_ENDED < '03/FEB/14'
ORDER BY TRIP_LOAD_STARTED;

The above querry gives me the output as follows:

Start_Time     End_Time     Time Diff bw Start and End

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14           12

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14         19

02/FEB/14    02/FEB/14         19

02/FEB/14    02/FEB/14         19

It shows two dates for which I want to get the average time in minutes for each date group by the start time.

I am using the following querry which probably is giving me the average of the two days mentioned in where clause

select  floor(avg(( TRIP_LOAD_STARTED - TRIP_LOAD_ENDED) * 1440)) " Average time Minutes "

from gate2gate_activity_vw

Where TRIP_LOAD_STDT >= '01/FEB/14'

AND TRIP_LOAD_STDT < '03/FEB/14';

Average Time in Minutes

29

Can anyone help me to calculated Daily Average time between Start and EndTime.

Kind regards,

mhamidch

Moazzam

Is this what you want:

    SELECT trunc(TRIP_LOAD_STARTED),trunc(TRIP_LOAD_ENDED),

    floor(avg(ceil(( TRIP_LOAD_ENDED - TRIP_LOAD_STARTED) * 1440))) Diff

    FROM GATE2GATE_ACTIVITY_VW

    WHERE TRIP_LOAD_STARTED >= to_date('01/FEB/2014','dd/MON-YYYY')

    AND TRIP_LOAD_ENDED < to_date('03/FEB/2014','dd/MON-YYYY')

    GROUP BY trunc(TRIP_LOAD_STARTED), trunc(TRIP_LOAD_ENDED)

Always post the sample create table and insert statements to verify the results and use to_date function with date format.

Message was edited by: Moazzam

Pardha Saradhi Meda

hi,

Please provide the time as well along with the dates to calculate the exact time,

DD/MM/YYYY hh24:mm:si format

Thank you.

Moazzam

Is this what you want:

    SELECT trunc(TRIP_LOAD_STARTED),trunc(TRIP_LOAD_ENDED),

    floor(avg(ceil(( TRIP_LOAD_ENDED - TRIP_LOAD_STARTED) * 1440))) Diff

    FROM GATE2GATE_ACTIVITY_VW

    WHERE TRIP_LOAD_STARTED >= to_date('01/FEB/2014','dd/MON-YYYY')

    AND TRIP_LOAD_ENDED < to_date('03/FEB/2014','dd/MON-YYYY')

    GROUP BY trunc(TRIP_LOAD_STARTED), trunc(TRIP_LOAD_ENDED)

Always post the sample create table and insert statements to verify the results and use to_date function with date format.

Message was edited by: Moazzam

user8167598

try this:

select trunc(start_date),average(time_diff)

from table name

where trunc(startdate) = trunc(to_date('19-MAY-2014','DD-MON-YYYY')) -- remove time portion for grouping

group by trunc(start_date)

SKP

First of all alter your date format

ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

Then Run your Query.

Post your query Output.

Because you are calculating on Minutes This will give more insight to your data to provide a solution,

user10903866

Thanks Moazzam that is what I needed.

hamidch