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

Next Steps

DATEADD and DATEDIFF can be used to calculate SQL Server datetime values


Dig Deeper on Oracle development languages

Data Management
Business Analytics
  • The 3 daily Scrum questions

    The 2020 Scrum Guide removed all references to the three daily Scrum questions, but does that mean you shouldn't ask them anymore?

  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

Data Center
Content Management