Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Average time of the date timestamp column

syed haiderFeb 14 2024 — edited Feb 14 2024

Hi,

I'm trying to find the average durations in minutes of the date_event table. Any feedback is greatly appreciated. Thank you.

The average session duration is calculated as the total duration of all sessions divided by the total number of sessions. In this case, there are 5 sessions. I think, for the total duration I would need to find the difference between the min date_event and max date_event in minutes then divide with a total of 5 sessions. Would you please help me with the code? I appreciate it.

(Please note that the actual data is stored in the format of "MM/DD/YYYY HH24:MI:SS.FF3 AM" (2/14/2023 07.36.35.000000 AM), but I'm not able to insert the date in that format.)

create table temp_table
(date_event timestamp(6));

insert into temp_table (date_event)
values (to_char(cast(sysdate - (1/24) as timestamp),'DD-MON-YYYY HH24:MI:SS.FF3 AM'));
insert into temp_table (date_event)
values (to_char(cast(sysdate - (2/24) as timestamp),'DD-MON-YYYY HH24:MI:SS.FF3 AM'));
insert into temp_table (date_event)
values (to_char(cast(sysdate - (1/12) as timestamp),'DD-MON-YYYY HH24:MI:SS.FF3 AM'));
insert into temp_table (date_event)
values (to_char(cast(sysdate - (1/10) as timestamp),'DD-MON-YYYY HH24:MI:SS.FF3 AM'));
insert into temp_table (date_event)
values (to_char(cast(sysdate - (1/9) as timestamp),'DD-MON-YYYY HH24:MI:SS.FF3 AM'));
select * from temp_table;

This post has been answered by Barbara Boehmer on Feb 15 2024
Jump to Answer
Comments
Post Details
Added on Feb 14 2024
9 comments
243 views