Home » SQL & PL/SQL » SQL & PL/SQL » Average Function for Date Columns ( (Oracle10g ))
Average Function for Date Columns Wed, 23 April 2008 08:36
 weekend79 Messages: 188Registered: April 2005 Location: Islamabad Senior Member
HI

Tab_A have two timestemp columns Send_Time and Receipt_Time
To find the difference of two columns I do (Receipt_Time - Send_Time) as 3rd_Col

I wish to find average of this 3rd_Col in seconds i.e. avg (Send_Time - Receipt_Time)

Wishes

Re: Average Function for Date Columns [message #316009 is a reply to message #316007] Wed, 23 April 2008 08:44
 ThomasG Messages: 3202Registered: April 2005 Location: Heilbronn, Germany Senior Member
In the title you say "Date Columns", in the text you say "Timestamp Columns"

Are the columns date or timestamp?

If they are date the avg should get you the difference in Days. A day has 24 Hours * 60 Minutes * 60 Seconds
Re: Average Function for Date Columns [message #316105 is a reply to message #316007] Wed, 23 April 2008 15:13
 weekend79 Messages: 188Registered: April 2005 Location: Islamabad Senior Member
Ok my mistake the col is timestamp, can some one give me avg(Send_Time - Receipt_Time) in seconds?

Wishes
Re: Average Function for Date Columns [message #316134 is a reply to message #316007] Wed, 23 April 2008 19:27
 Kevin Meade Messages: 2102Registered: December 1999 Location: Connecticut USA Senior Member
let us see

a = 1
b = 3

average of a,b is (a+b)/2 but dates don't work like numbers

but if b is > then a then an alternative average of a,b is a+((b-a)/2)

a = sysdate
b = sysdate+2

average of a,b = a + ((b-a)/2)

give it a try
Re: Average Function for Date Columns [message #316148 is a reply to message #316105] Wed, 23 April 2008 23:18
 Michel Cadot Messages: 65079Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 Quote: can some one give me avg(Send_Time - Receipt_Time) in seconds?

Did you try "avg(Send_Time - Receipt_Time)"? With the correction given by Thomas:
 Quote: ...the difference in Days. A day has 24 Hours * 60 Minutes * 60 Seconds

Regards
Michel
Re: Average Function for Date Columns [message #316160 is a reply to message #316007] Thu, 24 April 2008 00:36
 weekend79 Messages: 188Registered: April 2005 Location: Islamabad Senior Member
I think i fail to explain my problem to you so make it simple
Please ignore all that i said before.

Now there is a single timestemp Column say Col_1 having 100 records
i need average of this column in seconds.

Wishes
Re: Average Function for Date Columns [message #316162 is a reply to message #316160] Thu, 24 April 2008 00:44
 Michel Cadot Messages: 65079Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 Quote: Now there is a single timestemp Column say Col_1 having 100 records i need average of this column in seconds.

This is meaningless.
The same as what is the average of birh dates in a classroom in seconds. Can you anser this? If yes, then you answer your question. For myself I can't.

Edit1: If you'd post a test case, a table with say 10 rows and tell the result you want with this, maybe we (and you) may know and understand the question.

Edit2: Or maybe you mistake timestamp (which is date and time) for interval of time?

Regards
Michel

[Updated on: Thu, 24 April 2008 00:54]

Report message to a moderator

 Previous Topic: Help in NOT EXISTS Next Topic: doubt on functions (merged)
Goto Forum:

Current Time: Thu Jul 20 19:55:37 CDT 2017

Total time taken to generate the page: 0.20272 seconds