Getting weekly totals


#1

Hi there :slight_smile:

As a feature request, I’d love to be able to get a weekly total of active time and afk time.

In the meantime though, how could I go about getting this (without manually summing up each day)?

I’m happy to run a query against the database if necessary. How does one access the embedded database anyways?


#2

I have been working on making this easier actually! I have a set of PRs on Github which will land once we start working on activitywatch 0.8 next year which will make queries to the database a lot easier. This will make it possible to make use a kind of query language to query ActivityWatch buckets and and doing merging/transforming/chunking on events from multiple buckets. This is possible on 0.7 as well, but we were not happy with how we implemented it so it will change in 0.8.

Once I’m back home I can describe on how to do it in 0.7 in the meantime. It will be represented as JSON now, but once 0.8 lands it will hopefully be supported to visualize weekly summaries in the WebUI.


#3

Post this (but replace windowbucket and afkbucket with your own bucketnames) to 127.0.0.1:5600/api/0/views/myview

{
  "name": "myview_active",
  "query": {
    'chunk': 'app',
    'cache': true,
    'transforms':
    [{
      'bucket': windowbucket,
      'filters':
      [{
        'name': 'timeperiod_intersect',
        'transforms': [{
          'bucket': afkbucket,
          'filters': [{
            'name': 'include_keyvals',
            'key': 'status',
            'vals': ['not-afk'],
          }]
        }]
      }]
    }]
  }
}

After that you can use a get request on 127.0.0.1:5600/api/0/views/myview?start=2017-12-11&end=2017-12-18 to get a JSON summary of all events last week.

If you only want total active/afk time, I think this query should work instead

POST 127.0.0.1:5600/api/0/views/myview_active

{
  "name": "myview_active",
  "query":  {
    "chunk": "status",
    "cache": true,
    "transforms": [{
      "bucket": afkbucket
    }]
  }
}

GET 127.0.0.1:5600/api/0/views/myview_active?start=2017-12-11&end=2017-12-18