Geek asks and answers

Mysql average time query

I play a lot of board games and I maintain a website / database that keeps track of a few statistics. One of the tables tracks different times. The structure is as follows:

  • gameName (text - the name of the board game)
  • numPeople (int - the number of people who played)
  • timeArrived (timestamp is the time when we arrived at the house in which we play the game).
  • beginSetup (timestamp - the time when we start customizing the game)
  • startPlay (timestamp - game start time)
  • gameEnd (timestamp - end time of the game)

Basically, what I want to do is use these times to get interesting / useful information (for example, which game takes the longest on average, which game takes the longest on average, which game is the longest from arrival to the end etc.). I usually rely too much on PHP, and I just make a choice * ... and grab hold of some PHP calculations all the time to find all the statistics, but I know that MySQL can do all this for me with a query. Unfortunately, I get lost when it comes to more complex queries, so I need help.

I would like some examples of several queries, and I hope I can find other average queries of the time when someone starts me. What would a query look like over time on average to play a board game? How about a quick game / time to adjust on average?

Additional information: drew010 - You start me very well, but I do not get the expected results. I give you real examples ... I have a game called Harper and it has been played twice (so there are two entries in the database over time). Here's what the times look like:

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 run the query that you provided to me (and I convert seconds to hours / minutes / seconds), I get these results (sorry, I do not know how to make a cool table that you made):

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

1 24 - 3 34 . , ?

+5
2

, , , :

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;

, :

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

8 , , , .

, , , , . , , .

+6

- , , - :

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

More articles:


All Articles