如何在mongodb中按周分组文档

How do i calculate the total hours spend by each user on the basis of week and my week start from Friday to Thursday.

我如何计算每个用户在周和周四开始的周和周的总小时数。

Records

记录

{
  "_id" : ObjectId("5a69cd076b2beec65fa900e9"),
  "clock_date" : ISODate("2018-01-13T00:00:00.000Z"),
  "clock_in_time" : "10:40:53",
  "clock_out_time" : "12:40:53", 
},
{
  "_id" : ObjectId("5a69cd076b2beec65fa900e9"),
  "clock_date" : ISODate("2018-01-12T00:00:00.000Z"),
  "clock_in_time" : "10:00:53",
  "clock_out_time" : "10:40:53", 
},
{
  "_id" : ObjectId("5a69cd076b2beec65fa900e9"),
  "clock_date" : ISODate("2018-01-20T00:00:00.000Z"),
  "clock_in_time" : "10:20:00",
  "clock_out_time" : "12:40:53", 
},
{
  "_id" : ObjectId("5a69cd076b2beec65fa900e9"),
  "clock_date" : ISODate("2018-01-21T00:00:00.000Z"),
  "clock_in_time" : "10:40:53",
  "clock_out_time" : "11:40:53", 
},

Result to be like

结果就像

 {
  week:'Jan 05, 2018 to Jan 11, 2018'
  totalhourse : 4
 },
 {
  week:'Jan 12, 2018 to Jan 18, 2018'
  totalhourse : 2:23
 },

. . . . .

。。。。。

1 个解决方案

#1

Summing up the total hours with this schema is going to be tough and we should have a lot of calculations, since you have captured the time in and time out as strings. To find the difference we need to segregate hours:minutes:seconds and then we have to find the difference.

总结这个模式的总小时数将是艰难的，我们应该进行大量的计算，因为你已经将时间和时间作为字符串捕获。为了找到差异，我们需要隔离小时：分钟：秒然后我们必须找到差异。

"clock_in_time" : "10:40:53",
"clock_out_time" : "12:40:53",

Alternatively you can modify your schema to capture the in time and out time as Date with time stamp.

或者，您可以修改架构以将时间和时间捕获为带有时间戳的日期。

"clock_in_time" : ISODate("2018-01-13T10:40:53.123+05:30"),
"clock_out_time" : ISODate("2018-01-13T12:40:53.121+05:30"),

If this modification is done then you can use aggregation pipeline to get the desired result

如果完成此修改，则可以使用聚合管道来获得所需的结果

In aggregation pipeline first

在聚合管道中首先

Use $match and $and to filter the documents within the date range(here you can feed in week start date and week end date)

使用$ match和$并过滤日期范围内的文档（这里您可以在周开始日期和周结束日期提供）
then use $subtract with $project

然后使用$ subtract with $ project

Please note this query will give you only one week's result at a time

请注意，此查询一次只能给您一周的结果

References:

参考文献：

Subtracting time

减去时间

$match with $and operator

$与$和运算符匹配

#1

"clock_in_time" : "10:40:53",
"clock_out_time" : "12:40:53",