计算一列时间戳之间的持续时间 [英] Calculate duration between timestamps in one column
问题描述
我的时间时钟输出到SQL表，其中一列用于输入和输出时间戳，员工名称在另一列中.我希望能够通过Google表格脚本中的SQL查询来计算每位员工的每日工作时间.
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.
这是我拥有的:
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
我尝试将MIN()和MAX()与GROUP BY一起使用，但它们没有将日期分组在一起，这是我最新的SQL查询:
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
哪个输出:
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.
推荐答案
如果您只想以现有方式将其放入电子表格中，则可以采用这种方式进行计算.您所要做的就是选择包含数据的列，并在其右侧选择一个空列，然后运行此功能，它将复制所有以小时为单位的时差.进入空列.
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.
这就是现在的样子
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
这篇关于计算一列时间戳之间的持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
