PHP Laravel - Finding the quantity of seconds in each hour between two timestamps

Question

I'm trying to track the time someone is spent logged onto a system. I need to be able to group the total seconds spent logged in into hours. A user might have a logged on and logged off event that will span across multiple hours but I need to ensure that correct amount of seconds are logged against the correct hour.

For example, if a user were to log on a 17:55 and log off at 18:10 the user would have 300 seconds in hour 17:00 and 600 seconds in hour 18:00

I have searched absolutely everywhere and cannot find a solution that doesn't involve a cumbersome while loop, I'm working with a large data set so it must be efficient.

If anyone has come across this problem before or has any ideas that would be greatly appreciated.

Answer 1

You could use a log file, so WRITE to this file when a user logs in or out and print the timestamp in that exact moment.

Then you would query by reading in this file and group by that criteria, by creating a date diff in seconds.

More concrete information would be valuable.

Answer 2

So another performant approach would be using raw SQL. I'm supposing you are using a table named user_sessions or so.

$results = DB::table('user_sessions')
    ->select(DB::raw('HOUR(login_time) AS hour,    SUM(TIME_TO_SEC(TIMEDIFF(logout_time, login_time))) AS seconds'))->whereTime('login_time', '>=', $loginTime)
    ->whereTime('logout_time', '<=', $logoutTime)
    ->groupBy(DB::raw('HOUR(login_time)'))
    ->get();

$formattedResults = [];
foreach ($results as $result) {
    $formattedResults[] = [
        'hour' => $result->hour,
        'seconds' => $result->seconds,
    ];
}

return json_encode($formattedResults);

I hope this helps. Or at least gives you an idea .