Login ProductsSalesSupportDownloadsAbout |
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 |
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 PM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
Actually you can search for me and NULL on the emb ones as well - that thread was great fun! Roy Lambert |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |