Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Like to have Avg(Date_Col)
Mon, Jul 8 2013 1:19 PMPermanent Link

Barry

I don't understand why Avg can't average a date column. I know Tim has done that deliberately, and I don't understand the reason behind it.

Let's say I have a database with 'n' branches and they work on a project to ship their respective part. I'd like to do a simple SQL statement like:

select Project_Num, Avg(Ship_Date) from Project group by Project_Num;

to give me the average ship date of each project. Any number of branches may be working on the project. It would be so simple to do an Avg(Ship_Date) to get the average date.

But from reading prior posts I have to do:

SELECT DATE '1901-01-01' + CAST(AVG(Ship_Date - DATE'1901-01-01') AS INTERVAL DAY) AS AverageShipDate FROM Project

This seems unnecessarily complicated to my way of thinking.
Can anyone explain the reason behind it?

TIA

Barry
Mon, Jul 8 2013 1:59 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Barry


I seem to remember this one being discussed before and the concept of an average date is essentially meaningless (usefull sometimes but meaningless).

Adam Brett in a post back in 2011 said

<<I ended up using MAX & MIN and then dividing by 2, which isn't exactly an average, but definitely goes some way to getting a useful figure.>>

Coming from a manufacturing background I'd say that the ship date is the date the last bit was shipped - things were useless until then. Pick on whoever was last to ship and kick them UNLESS their shipping date was on or before the required date. I also used to kick anyone who shipped to early as well <vbg>

Roy Lambert
Mon, Jul 8 2013 3:25 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Barry,

<< I don't understand why Avg can't average a date column. I know Tim has
done that deliberately, and I don't understand the reason behind it. >>

My answer may sound snarky, but it isn't:  what is an average date ?

The problem here is that you're not talking about an *amount*, but an
absolute point in time (this difference is the whole rationale behind
day-time interval values).  It would be the same as averaging birthdays
(points in time) instead of ages (amounts) - doing so would not make any
sense.  Allowing averages on dates would be exposing implementation details
about how dates are computed/stored, which is a no-no in SQL.  It's the same
reason why indexes are left out of the SQL standard.

If you want to average such data, then you have to convert it into a form
that represents an amount, which is what you've done with your query
example.

Tim Young
Elevate Software
www.elevatesoft.com
Sun, Jul 14 2013 5:39 PMPermanent Link

Barry

Tim,

Thanks for the explanation. It makes sense from a mathematical perspective.

I just read Roy's thread "Addng date and time together" from 2008, and that was also a treat to see rational logic vs the real world view (aka common sense). I haven't seen a slug fest like that since I watched Godzilla vs King Ghidorah. (I'm rooting for you Roy!)

I shall explain to my boss that averaging dates is logically impossible to achieve in this or any other time continuum, and to forget about adding a date and time column together (if I use an analogy that it is similar to crossing the streams in Ghost Busters he may get the concept, especially if I mention it could create a date+time vortex).

If on the other hand his eyes start to glaze over and he begins to drool, I know this will give me enough time to sneak out and write a SQL function to achieve what he wants. I just hope the ANSI police don't show up at work one day, because he will just disavow any knowledge of my actions.

Barry
(Some levity for a Sunday)
Mon, Jul 15 2013 3:39 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Barry


1. If you want a real slugfest search for any post by me that mentions NULL on these ngs or Pointers on the emb ones

2. Try and convince your boss that a date range would be more useful (earliest shipped and latest shipped). Not sure how to phrase that in Ghostbuster's speak but maybe something about the river of slime?


Roy Lambert
Mon, Jul 15 2013 4:11 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Barry


Actually you can search for me and NULL on the emb ones as well - that thread was great fun!

Roy Lambert
Image