Calculate duration between timestamps in one colum

Question

My time clock outputs to a SQL table with a single column for the time stamps in and out, and the employee name in another column. I would like to be able to calculate the daily hours for each employee with a SQL query from Google Sheets script.

Heres what I have:

ID      Name            Timestamp
1023    Emily Bromley   2016-08-15 9:00:00
1023    Emily Bromley   2016-08-15 17:00:00
1023    Emily Bromley   2016-08-16 9:00:00
1023    Emily Bromley   2016-08-16 17:00:00
1023    Emily Bromley   2016-08-17 9:00:00
1023    Emily Bromley   2016-08-17 15:30:00
1023    Emily Bromley   2016-08-22 9:00:00
1023    Emily Bromley   2016-08-22 16:00:00
1023    Emily Bromley   2016-08-23 8:55:00
1023    Emily Bromley   2016-08-23 16:58:00
1023    Emily Bromley   2016-08-24 8:20:00
1023    Emily Bromley   2016-08-24 16:59:00
1023    Emily Bromley   2016-08-25 9:52:00
1023    Emily Bromley   2016-08-25 16:59:00
1023    Emily Bromley   2016-08-29 9:11:00
1023    Emily Bromley   2016-08-29 16:57:00

What I would like to do is group the dates together and find the duration of the timestamps:

ID      Name            Day and Duration
1023    Emily Bromley   2016-08-15 8
1023    Emily Bromley   2016-08-16 8
1023    Emily Bromley   2016-08-17 6.5
1023    Emily Bromley   2016-08-22 7
1023    Emily Bromley   2016-08-23 8.1
1023    Emily Bromley   2016-08-24 8.5
1023    Emily Bromley   2016-08-25 8.1
1023    Emily Bromley   2016-08-29 8.2

Ive tried using MIN() and MAX() with GROUP BY but they don't group the days together, here's my latest SQL query:

SELECT  access_user.user_id, 
        access_user.first_name + ' ' + access_user.last_name,
        MIN(user_time_log.log_time),
        MAX(user_time_log.log_time)
FROM    access_user
        INNER JOIN user_time_log
        on access_user.user_id=user_time_log.user_id
GROUP BY access_user.user_id,
         access_user.first_name + ' ' + access_user.last_name

Which outputs:

1023    Emily Bromley   2016-08-15 9:00:00  2017-02-17 13:01:00

There are other employees of course but everyone only clocks in and out once a day. I have googled for a while now but haven't come across anything real similar to my issue. Any help is much appreciated.

Answer 1

If you want to just put it in a spreadsheet the way you have then you can calculate this way. All you have to do is select the column that has data and an empty column to the right of it and then run this function and it will copy in all of the time differences in hours. Into the empty column.

function timeDiff()
{
  var rng = SpreadsheetApp.getActiveRange();
  var hour = 3600000;
  var rngA = rng.getValues();
  for( var i = 0; i < rngA.length ;i++)
  {
    if(i>0)
    {
      rngA[i][1]= (new Date(rngA[i][0]).getTime() - new Date(rngA[i-1][0]).getTime())/hour;

    }


  }
  rng.setValues(rngA);
}

Here's what it looks like after you run the data and format the output. I also formatted your datetime as a datetime and sorted by ascending dates.

Here's what that looks like now

1023    Emily Bromley   8/15/2016 9:00:00   
1023    Emily Bromley   8/15/2016 17:00:00  8.00
1023    Emily Bromley   8/16/2016 9:00:00   16.00
1023    Emily Bromley   8/16/2016 17:00:00  8.00
1023    Emily Bromley   8/17/2016 9:00:00   16.00
1023    Emily Bromley   8/17/2016 15:30:00  6.50
1023    Emily Bromley   8/22/2016 9:00:00   113.50
1023    Emily Bromley   8/22/2016 16:00:00  7.00
1023    Emily Bromley   8/23/2016 8:55:00   16.92
1023    Emily Bromley   8/23/2016 16:58:00  8.05
1023    Emily Bromley   8/24/2016 8:20:00   15.37
1023    Emily Bromley   8/24/2016 16:59:00  8.65
1023    Emily Bromley   8/25/2016 9:52:00   16.88
1023    Emily Bromley   8/25/2016 16:59:00  7.12
1023    Emily Bromley   8/29/2016 9:11:00   88.20
1023    Emily Bromley   8/29/2016 16:57:00  7.77