Q
Problem solve Get help with specific problems with your technologies, process and projects.

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


This was last published in July 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close