CODEWITHSUNDEEP
sqloracle-database
BD12
BD12

Reputation: 107

Oracle SQL - Find average time from datetime field either side of midnight

first time posting a question so apologies if not clear.

I want to find the average delivery time of files based on a generation datetime field compared to a business date. The problem I have is that the files could be delivered before or after midnight which is throwing out my averages.

This is the dataset i'm testing with;

select ID, business_date, generation_date
from table
where business_date >= to_date('20190705','yyyyMMdd hh24mi') and
      subscription_id = 'Report 1'

ID  BUSINESS_DATE   GENERATION_DATE
Report 1    09/07/2019  09/07/2019 23:02
Report 1    08/07/2019  09/07/2019 01:10
Report 1    05/07/2019  05/07/2019 20:58
Report 1    10/07/2019  10/07/2019 21:54

The generation_date on the second row is after midnight so when I try the below code it gives an incorrect average.

select id, 
to_char(trunc(sysdate) + 
               avg(cast(generation_date as date) - cast(trunc(generation_date) as date))
               , 'hh24mi') as ""Average_Delivery_Time""
from table
where a.business_date >= to_date('20190705','yyyyMMdd hh24mi')
and id = 'Report 1'
group by id

ID  Average_Delivery_Time
Report 1    16:46:27

I have thought about spliting out the date and time and then performing some calculation based on the business_date but i'm sure there must be a better way to find the average time (taking into consideration the different dates).

Correct Average Time: 22:46:26

Any help on how to do this in SQL would be much appreciated.

see above

see above

Upvotes: 1

Views: 220

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Does this do what you want?

avg(generation_date - trunc(business_date))

I am not sure why you are converting what look like dates into dates.

Upvotes: 1

Related Questions