I've been tasked with making a stats "viewer" for one of our server's gamemodes. Its been going great, but the only issue I have is converting the time played to hours:minutes:seconds.
The developer who made the actual gamemode stores the time played in MySQL in milliseconds. Sadly, many players have enough time to easily cause an integer overflow (when calculated in PHP).
Now, I'm wondering what is the easiest and most efficient way to resolve this. Is there a method to do this in the MySQL query that I am not aware of?
Let's say that you have 36001316000 milliseconds:
SET @miliseconds = 36001316000;
SET @s = @miliseconds / 1000;
SET @hours = FLOOR(@s / 3600);
SET @minutes = FLOOR(@s / 60 - @hours * 60);
SET @seconds = ROUND(@s - @hours * 3600 - @minutes * 60);
SELECT @hours, @minutes, @seconds;
this will return 10000 hours, 21 minutes & 56 seconds.
How will you store those information depends on how you will use it. If it's just for informational purpose, or PHP based calculations you can store it in varchar, if you need to do calculations in MySQL based on it, you can store it in 3 different integer fields.