Question 179418 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 🙃.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search