Home » SQL & PL/SQL » SQL & PL/SQL » Average Function for Date Columns ( (Oracle10g ))
Average Function for Date Columns [message #316007] Wed, 23 April 2008 08:36 Go to next message
weekend79
Messages: 188
Registered: 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)

Please advise how?

Wishes
Jawad

Re: Average Function for Date Columns [message #316009 is a reply to message #316007] Wed, 23 April 2008 08:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: 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 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: 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
Jawad
Re: Average Function for Date Columns [message #316134 is a reply to message #316007] Wed, 23 April 2008 19:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: 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.

Please advise how?

Wishes
Jawad
Re: Average Function for Date Columns [message #316162 is a reply to message #316160] Thu, 24 April 2008 00:44 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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: Sat Dec 03 11:45:36 CST 2016

Total time taken to generate the page: 0.09282 seconds