Mysql query to average time

Question

I play a lot of board games and I maintain a site/database which keeps track of several statistics. One of the tables keeps track of various times. It's structure looks like this:

• gameName (text - the name of the board game)
• numPeople (int - the number of people that played)
• timeArrived (timestamp - the time we arrived at the house we are playing the game)
• beginSetup (timestamp - the time when we begin to set up the game)
• startPlay (timestamp - the time we actually start playing the game)
• gameEnd (timestamp - the time the game is finished)

Basically, what I'm wanting to do is use these times to get some interesting/useful info from (like what game on average takes the longest to set up, what game on average takes the longest to play, what game is the longest from arrival to finish, etc...) Normally, I rely way too much on PHP and I would just do a select * ... and grab all the times then do some PHP calculations to find all the stats but I know that MySQL can do all this for me with a query. Unfortunately, I get pretty lost when it comes to more complex queries so I'd like some help.

I'd like some examples of a couple queries and hopefully I can figure out other average time queries once someone gets me started. What would the query look like for longest time on average to play a board game? What about quickest game/time to set up on average?

Additional Info: drew010 - You have me off to a great start but I'm not getting the results I'd expected. I've give you some real exmples... I've got a game called Harper and it's been played twice (so there are two records in the database with time entires). Here are what the times look like for it:

beginSetup(1) = 2012-07-25 12:06:03
startPlay(1) = 2012-07-25 12:47:14
gameEnd(1) = 2012-07-25 13:29:45

beginSetup(2) = 2012-08-01 12:06:30
startPlay(2) = 2012-08-01 12:55:00
gameEnd(2) = 2012-08-01 13:40:32

When I then run the query you provided me (and I convert the seconds into hours/minutes/seconds) I get these results (sorry, I don't know how to do the cool table you did):

gameName = Harper
Total Time = 03:34:32
...and other incorrect numbers.

From the numbers, the Average Total Time should be about 1 hour and 24 minutes - not 3 hours and 34 minutes. Any idea why I'd be getting incorrect numbers?

Answer 1

Here is a query to get the average setup time and play time for each game, hope it helps:

SELECT
  gameName,
  AVG(UNIX_TIMESTAMP(startPlay) - UNIX_TIMESTAMP(beginSetup)) AS setupTime,
  AVG(UNIX_TIMESTAMP(gameEnd) - UNIX_TIMESTAMP(startPlay)) AS gameTime,
  AVG(UNIX_TIMESTAMP(gameEnd) - UNIX_TIMESTAMP(beginSetup)) AS totalTime,
FROM `table`
GROUP BY gameName
ORDER BY totalTime DESC;

Should yield results similar to:

+----------+-----------+-----------+-----------+
| gameName | setupTime | gameTime  | totalTime |
+----------+-----------+-----------+-----------+
| chess    | 1100.0000 | 1250.0000 | 2350.0000 |
| checkers |  466.6667 |  100.5000 |  933.3333 |
+----------+-----------+-----------+-----------+

I just inserted about 8 test rows with some random data so my numbers don't make sense, but that is the result you would get.

Note that this will scan your entire table so it could take a while depending on how many records you have in this table. It's definitely something you want to run in the background periodically if you have a considerable amount of game records.

Answer 2

For something like how long it took to set up you could write something like:

SELECT DATEDIFF(HOUR, BeginSetup, StartTime) -- in hours how long to set up