MySQL: Get the Average Time Difference for Datetime Records Tips, Tricks and Tutorials 09 OCT 2009

If your table contains two datetime columns for which you wish to calculate, and display in readable format, the average time difference for the entire table, the simplest (and probably fastest) way to achieve this is by doing the calculation in your SQL statement directly.

To achieve this you first work out the time difference between the two fields using the TIMESTAMPDIFF functionality (specifying seconds as the unit to work in). Then you run AVG across that result set which will group and return the average time difference for the table as whole. Finally, use SEC_TO_TIME to convert the average result (which is now in seconds) into a user friendly display string.

Putting this all together, you SQL statement should now look like this:

“SELECT SEC_TO_TIME ( AVG ( TIMESTAMPDIFF ( SECOND, `datetime1`,`datetime2`))) FROM tabletoprocess

… the output of which will be in the format 00:00:00

Nifty.

stopwatches

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.