AVG() function on a date field

I need to know how to use a avg function with the ship_date in the sales_order table.

Perhaps the first thing to do for this problem is to ask "What is an average date?" but I'll come back to that in a moment.

In order processing, one measure of efficiency is how long it takes from the day an order is placed until the day it is shipped. The lower this measure, the more efficient your order processing operation, and the more profitable your business (you cannot, for example, bill a customer's credit card until the goods have been shipped). An average for this measure can be obtained by --

select avg( ship_date 
          - order_date )
        as average_ship_days
  from sales_order

In the above query, a date interval will be calculated for each row, and the AVG() function will determine the average interval. This only works, however, in databases that allow you to subtract one datetime value from another to get an interval that can be used in an aggregate function meaningfully. Oracle, for example, defaults all such date calculations to days, so the average_ship_days result will be a number of days.

If you aren't sure your database automatically produces results in days for date calculations, look for some sort of date conversion function. For example, in MySQL you could use the TO_DAYS() function, which converts a date into a daynumber, which is the number of days since year 0000.

select avg( to_days(ship_date)
          - to_days(order_date) )
        as average_ship_days
  from sales_order

It doesn't matter what number the daynumber actually is, because when you subtract one daynumber from another, the difference is the number of days between them (assuming you don't use pre-Gregorian dates), and that difference is suitable for averaging.

However, I still haven't answered your original question. Is it possible to obtain the average of a column of dates? Perhaps. Some databases actually will let you write

select avg( ship_date )
        as average_ship_date
  from sales_order

Do not be surprised if this doesn't work. If it doesn't, you'll need to convert the dates to days inside the AVG() function. If it does work, it should give you a date that is somewhere between the earliest and latest date in the column. On the other hand, it might give you just some number which you will then have to convert to a date using a formatting function. Databases usually store dates and datetimes internally as some kind of large integer or floating point number, but they don't all use the same starting point (for example, SQL/Server uses January 1, 1900). So if the AVG() function produces a number, you'll have to convert it back to a date anyway.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.