Forum Stats

  • 3,814,896 Users
  • 2,258,927 Discussions
  • 7,892,883 Comments

Discussions

How to Calculate Average Daily Time from Start_Time and End_Time fields

user10903866
user10903866 Member Posts: 30
edited May 30, 2014 1:14AM in SQL & PL/SQL

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

Tagged:
user10903866

Best Answer

  • Moazzam
    Moazzam Member Posts: 1,356
    edited May 21, 2014 1:14AM Answer ✓

    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

    user10903866

Answers

  • 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
    Moazzam Member Posts: 1,356
    edited May 21, 2014 1:14AM Answer ✓

    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

    user10903866
  • user8167598
    user8167598 Member Posts: 114

    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
    SKP Member Posts: 844 Gold Badge

    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,

  • Thanks Moazzam that is what I needed.

    hamidch

This discussion has been closed.