Do you know of an efficient way to calculate the AVG (date) in Oracle9i? I have a complex query that returns predicted dates and I need to average them.
The bad news is: AVG only works on numbers. If you need to average anything else, the best you can do is map the values to numbers, average the numbers, and, if necessary and possible, map back to the original domain.
The good news is: That's quite easy with dates in any version of Oracle. Oracle's date arithmetic does it all for you: you don't even have to use TO_NUMBER or TO_DATE.
SELECT SYSDATE + AVG (predict_dt - SYSDATE) FROM table_q;
In Oracle, subtracting one date from another results in a number, equal to the number of days between them. (For example, if predict_dt is midnight at the beginning of tomorrow, and it's now 6:00 pm, then predict_dt minus SYSDATE is .25.) When you add a date d to a number n in Oracle, the result is another date, n days later than d. (For example, SYSDATE + .25 is the date .25 days in the future, that is, six hours from now.)
You may want to treat the average as a column in a query, alongside the predicted date and other values. In that case, join in a subquery:
SELECT q.predict_dt, s.avg_predict_dt, ... FROM table_q q, ( SELECT SYSDATE + AVG (predict_dt - SYSDATE) AS avg_predict_dt FROM table_q ) s ...;
What's so special about SYSDATE that we can use it as the center of our time reckoning system? Nothing at all: SYSDATE is completely arbitrary. You can use any date as point 0.
If you want to, you can use any of these:
- January 1, 4712 BCE
- July 4, 1776
- Your birthday
- January 1, 2000
The only thing that matters is that you use the same value when converting from a date to a number (predict_dt - x) and back again (x + AVG ...). I chose SYSDATE because it produces a date with only seven keystrokes.
Now, what if your query is so complex that it takes several minutes to run, and you're concerned about getting an average that's accurate down to the second? Don't worry about SYSDATE changing in the course of a SELECT statement: Oracle will use the time the query started as SYSDATE throughout the entire statement, even if it takes a noticeable amount of time to finish. (Similarly, in long-running INSERT and UPDATE statements, Oracle uses the ending time of the query as SYSDATE for all rows affected.)