|Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread|
|Messages 1 to 6 of 6 total|
|Like to have Avg(Date_Col)|
|Mon, Jul 8 2013 1:19 PM||Permanent Link|
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?
|Mon, Jul 8 2013 1:59 PM||Permanent Link|
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>
|Mon, Jul 8 2013 3:25 PM||Permanent Link|
Tim Young [Elevate Software]
Elevate Software, Inc.
<< 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
|Sun, Jul 14 2013 5:39 PM||Permanent Link|
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.
(Some levity for a Sunday)
|Mon, Jul 15 2013 3:39 AM||Permanent Link|
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?
|Mon, Jul 15 2013 4:11 AM||Permanent Link|
Actually you can search for me and NULL on the emb ones as well - that thread was great fun!