I have a table in database like this:
unit arrival_date departure_date
---- ------------- --------------
1 27/1/2017 08:01:20 a. m. 27/1/2017 08:04:27 a. m.
1 27/1/2017 08:05:35 a. m. 27/1/2017 08:09:28 a. m.
I need to calculate the average time difference between arrival_date and departure_date of users and show the result as hour,minute,second format(HH:MI:SS).
If I made this arrival_date - departure_date, I get the result in days, but I am struggling to get the average in hour, minutes and seconds.
The fields are DATE fields not TIMESTAMP fields.
Here's an example.
When subtracting two date datatype values, the result is number of days. It shows the INTER CTE. When you multiply it by 24 (number of hours in a day), 60 (number of minutes in an hour) and 60 (number of seconds in a minute), the result is number of seconds (DIFF_SECS).
AVERAGES CTE shows how to apply AVG function to previous results; nothing special in that, just pay attention that you have to GROUP it BY the UNIT column.
Finally, apply TO_CHAR formatting to calculation (some TRUNC and MOD calls in order to extract hours, minutes and seconds from the AVG_DIFF_SECS value).
I suggest you run each CTE separately, step by step, to easier follow the execution.
SQL> with test (unit, arr, dep) as
2 (select 1, to_date('27.01.2017 08:01:20', 'dd.mm.yyyy hh24:mi:ss'),
3 to_date('27.01.2017 08:04:27', 'dd.mm.yyyy hh24:Mi:ss')
4 from dual union all
5 select 1, to_date('27.01.2017 08:05:35', 'dd.mm.yyyy hh24:mi:ss'),
6 to_date('27.01.2017 08:09:28', 'dd.mm.yyyy hh24:Mi:ss')
7 from dual
8 ),
9 inter as
10 (select unit, (dep - arr) diff_days,
11 (dep - arr) * 24 * 60 * 60 diff_secs
12 from test
13 ),
14 averages as
15 (select unit,
16 avg(dep - arr) avg_diff_days,
17 avg((dep - arr) * 24 * 60 * 60) avg_diff_secs
18 from test
19 group by unit
20 )
21 select
22 to_char(trunc(avg_diff_secs / 3600), 'fm00') || ':' || -- hours
23 to_char(trunc(mod(avg_diff_secs , 3600) / 60), 'fm00') || ':' || -- minutes
24 to_char(mod(avg_diff_secs, 60), 'fm00') -- seconds
25 avg_diff_formatted
26 from averages;
AVG_DIFF_FORMATTED
--------------------
00:03:30
SQL>