Hey.
I use a table called `sessions` for storing player session data. It logs all the relevant information: their unique ID `id`, time of connection `jointime`, time of disconnection `leavetime` (and for other purposes, also their name and IP address).
Whenever a player disconnects, an entry is added to this `sessions` table.
INSERT INTO sessions(id,jointime,leavetime) VALUES(..., [JOIN UNIX TIMESTAMP], UNIX_TIMESTAMP())
Where [JOIN UNIX TIMESTAMP] is the UNIX time at their session start.
Depending on how your system is built up and how stable your server is, you might also want to have a backup routine for adding sessions which were terminated due to server crash.
To retrieve someone's online time on 15th of October (for example), we would need 2 UNIX timestamps:
1. 00:00:00 15th Oct 2014 (1413331200 UTC)
2. 00:00:00 16th Oct 2014 (1413417600 UTC)
(retrieved from
http://www.unixtimestamp.com/index.php, for this example)
Now I would run a query on the `sessions` table like this:
SELECT id,jointime,leavetime FROM sessions WHERE (jointime BETWEEN 1413331200 AND 1413417600) OR (leavetime BETWEEN 1413331200 AND 1413417600)
This query will return records of when the player either joined between midnight 15th and midnight 16th or when the player's disconnection time falls between the midnights.
This query will return rows even if the player was online
14th October 22:00 until 15th October 02:00 (4h)
or for example
15th October 23:30 until 16th October 04:00 (4h 30min)
I would then use some basic calculations to determine the exact online time of the player between the two timestamps.
pawn Код:
// let values jointime and leavetime hold the UNIX timestamps accordingly.
// let values startOfPeriod and endOfPeriod hold the timestamps retrieved above.
new onlineDuringPeriod = leavetime-jointime;
if(jointime < startOfPeriod) {
onlineDuringPeriod -= (startOfPeriod-jointime);
}
if(leavetime > endOfPeriod) {
onlineDuringPeriod -= (leavetime-endOfPeriod);
}
The code above can be written in different ways, but I hope my version gets the point across nicely. If the player joined before the midnight of 15th, subtract the time they spent online before the midnight of 15th from the total online time. Same for leaving after the midnight of 16th.
So in the variable onlineDuringPeriod you will have the amount of seconds they spent online between 15th 00:00 and 16th 00:00.
Sorry if I'm a little rusty
