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.
You might also enjoy:
-
Visual Basic .NET is very flexible with regards to its date/time functions and functionality. Displaying or using the date/time in a format you wish to forc ... -
Sometimes it is quite handy to see which records based on a particular key appear more than once in a database. The trick here is that we are focusing on a ... -
There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when ... -
To get a list of user accounts on a MySQL server instance, we need to make a SELECT call to the mysql.user table. First, fire up MySQL in your terminal ...
-
In the middle of writing a quick support page email notification system for Touchwork, I realised that I needed to add a check which would limit a user from ...
