CODEWITHSUNDEEP
mysqlsqldatabase
erogol
erogol

Reputation: 13624

How can I compute difference in time given minute and second columns in a table?

I have a table of a sport match events. Each row annotated with the minute and the second of that particular event. Only one event type shows that game stopped. My aim is to compute the total game time of action where the ball is in the game and the game is running. My intention is subtracting the total time passed between all game stopped events and one next events from the whole game time. In that way, I only reside with the time passed while ball is in the game.

My question is, how can I convert these two separate columns (minute, second) into a proper MM:SS format without hour segment and subtract one event's time from the following event time?

event_id    minute    second
A           0         1
B           0         15
GameStop    0         45
B           0         55 subtract above row from this row, find 10 sec off time
C           1         15
D           1         37
GameStop    1         42
C           2         47 subtract above row from this row, find 55 sec off time
....
GameEnd     105       00

105min - (10sec+55sec+....) = Total Game Time where Game is not paused

Other than the time format problem, regarding to the whole problem, any suggestion is welcome.

Upvotes: 0

Views: 55

Answers (1)

Raul
Raul

Reputation: 19

Event Id Event StartTime EndTime Difference

ID1 GameSession 1:00:00 AM 1:00:45 AM 0:00:45

ID1 GamePause 1:00:45 AM 1:00:55 AM 0:00:10

ID1 GameSession 1:00:55 AM 1:01:42 AM 0:00:47

ID1 GamePause 1:01:42 AM 1:02:47 AM 0:01:05

ID1 GameSession 1:02:47 AM 2:45:00 PM 1:42:13

The better way is store the event in above format. For Each session if it is game session or pause session , post the start and end time in same row. Also post the difference between the start and end into another column.

Using below query you can get whatever u want.

Total time :

select sum(Difference), EventId from Event_table group by EventId;

Game Session Time:

select sum(Difference), EventId,Event from Event_table where Event= 'GameSession'group by EventId,Event;

Pause Session Time:

select sum(Difference), EventId,Event from Event_table where Event= 'GamePause'group by EventId,Event;

Hope this will help !!!

Upvotes: 1

Related Questions