Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate average time
How to calculate average time [message #413345] Wed, 15 July 2009 06:11 Go to next message
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 #413346 is a reply to message #413345] Wed, 15 July 2009 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does the average of dates is and mean?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to calculate average time [message #413348 is a reply to message #413345] Wed, 15 July 2009 06:25 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Check whether this helps you or not.

By
Vamsi

[Updated on: Wed, 15 July 2009 06:27]

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 Go to previous messageGo to next message
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 #413364 is a reply to message #413348] Wed, 15 July 2009 07:12 Go to previous messageGo to next message
mighty
Messages: 3
Registered: July 2009
Junior Member

Thanks Vamsi.


Sorry that i did't mention that i need this query in sqlite.



Re: How to calculate average time [message #413369 is a reply to message #413364] Wed, 15 July 2009 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
But this is an Oracle forum and you didn't say if you want or not the date part to be taken into account.

Regards
Michel
Re: How to calculate average time [message #413372 is a reply to message #413369] Wed, 15 July 2009 07:37 Go to previous message
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.


Previous Topic: Dynamic sql
Next Topic: Check Constraint
Goto Forum:
  


Current Time: Tue Feb 11 11:37:35 CST 2025