How to calculate average time [message #413345] |
Wed, 15 July 2009 06:11  |
mighty
Messages: 3 Registered: July 2009
|
Junior Member |
|
|
Hi,
Iam trying to calculate average time,but unable to come up with that.
I have a column called edate which stores date and time in
DD/MM/YYYY HH:mm format.
Lets Say,
edate
10/04/2009 10:10
12/04/2009 09:20
13/04/2009 08:04
I need to get average time from these three values,thru a sql query.
Please help with this.
Thanks
MYT
[Updated on: Wed, 15 July 2009 06:24] by Moderator Report message to a moderator
|
|
|
|
|
Re: How to calculate average time [message #413359 is a reply to message #413345] |
Wed, 15 July 2009 06:50   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Assuming that you want to totaly ignore the Date part, and just want the average number of hours past the previous midnight for the values shown, you can do it like this:with src as (select to_date('10/04/2009 10:10','dd/mm/yyyy hh24:mi') col_1 from dual union all
select to_date('12/04/2009 09:20','dd/mm/yyyy hh24:mi') col_1 from dual union all
select to_date('13/04/2009 08:04','dd/mm/yyyy hh24:mi') col_1 from dual)
select to_char(trunc(sysdate) + avg(col_1 - trunc(col_1)),'hh24:mi') avg_hours
from src;
|
|
|
|
|
Re: How to calculate average time [message #413372 is a reply to message #413369] |
Wed, 15 July 2009 07:37  |
mighty
Messages: 3 Registered: July 2009
|
Junior Member |
|
|
@JRow
thanks for your input.
@Mic
pls excuse for posting at Wrong forum
You may aware that Sqlite and Sql are almost same.
Date part of the column is not considered.
Hope you got my query now.
|
|
|