0

On a small test-site i'd like to log the approximately period of time a user actively spends on certain link groups per day. Thereofore on any user click i save a document like this:

Example document: 

{
"routeGroup": "a",
"time": new Date()
}

The idea is to filter all documents of a certain day, sort these by time and return the continuous period of time a user has spend on a routeGroup.

Example:

Given the following Documents (already sorted by day and filtered by time):

{"routegroup": "a", "time": 11:30:00},
{"routegroup": "a", "time": 11:31:00},
{"routegroup": "a", "time": 11:32:00},

{"routegroup": "b", "time": 11:33:00},
{"routegroup": "b", "time": 11:34:00},

{"routegroup": "a", "time": 11:35:00},

{"routegroup": "b", "time": 11:36:00},

{"routegroup": "a", "time": 11:37:00},
{"routegroup": "a", "time": 11:38:00},
{"routegroup": "a", "time": 11:39:00}

So the user has first spend 3 minutes in group "a" followed by 2 minutes in group "b", 1 minute in group "a" and so on.

These periods of time are sensitive up to seconds and i'd then like to sum and return something like:

// total time spend in routeGroups on day x

{"routegroup": "a", "timeTotal": "7:00"},
{"routegroup": "b", "timeTotal": "3:00"}

Question: Is this possible and if, how?

Thanks in advance for your help!

Muff

Raggamuffin
  • 699
  • 1
  • 6
  • 19
  • If there is an item logged for every minute then surely you just need the total count per group! Yes? Don't expect aggregation to efficiently happen in browser client code. This is a job for the server, and when you break down the problem to it's simplistic meaning, then the very simple answers are easy to find. – Neil Lunn Apr 14 '16 at 10:20
  • The interval of minutes i chose for demonstration purposes - seconds should be respected as well. The query is executed on the server. – Raggamuffin Apr 14 '16 at 10:53
  • Like @NeilLunn said, if your logging interval is constant (be it X minutes or X seconds), the answer is just the total count per group. So, the question here is if your events interval is constant or variable. – joao Apr 14 '16 at 12:31
  • Oh no, it's variable. – Raggamuffin Apr 14 '16 at 16:21

1 Answers1

1

The aggregation framework can give you your expected answer:

db.times.aggregate([
    {$group: {_id: '$routegroup', timeTotal: {$sum : 1}}},
    {$project: {_id: 0, routegroup: '$_id', timeTotal: 1}}
])

This does assume that each record stored is approximately 1 minute of user activity and that you're mostly concerned with the total time per day, rather than maximum continuous time (which your question/expected result implies is the case)

Kevin Blake
  • 434
  • 2
  • 5
  • Thanks, but the interval of minutes i just chose to simplify the example. The 'timeTotal' should be sensible for seconds. – Raggamuffin Apr 14 '16 at 10:45
  • I see... Are you able to modify your schema to make routegroup unique per visit, e.g.{"routegroup": "a", visitId: 1, "time": 11:30:00}, {"routegroup": "a",visitId: 1, "time": 11:31:00}, {"routegroup": "a",visitId: 1, "time": 11:32:00}, {"routegroup": "b",visitId: 2, "time": 11:33:00}, {"routegroup": "b",visitId: 2, "time": 11:34:00}, etc. Then you'd be able to modify the query to group by that and select min/max values and calculate the differences. If not, I would think you would have to do this outside of mongodb on your client once you have the sorted list – Kevin Blake Apr 14 '16 at 15:55
  • Ah ok, so that every block of entries of the same group get an id - i'll try that thanks! – Raggamuffin Apr 14 '16 at 16:27