Query - Category Sum per day

Support
#1

I am trying to write a query that will show me how much time I spend in an event category (for example games) per day. Ideally the output would look something like this:
5/29/20 : 2 hours
5/28/20 : 30 min
5/27/20 : 1 hour

What I have only shows me the sum for all of the time periods and doesn’t break them out by day. Here is what I have so far

window_events = query_bucket(find_bucket("aw-watcher-window_"));
afk_events = query_bucket(find_bucket("aw-watcher-afk_"));
window_events_active = filter_period_intersect(window_events, filter_keyvals(afk_events, "status", ["not-afk"]));
events = filter_keyvals_regex(window_events, "title", "\b.(Minecraft|RimWorld).");
no_youtube = filter_keyvals_regex(events, "title", "^((?!YouTube).)*$");
RETURN = sum_durations(no_youtube);

Is this even possible? If so any suggestions?

#2

Yes it’s possible, simply add more timeperiods to the query and in the response you will see that you get one entry in the array for each time period you requested. So it’s not specified in the query string itself, but in the timeperiod field.

I’m not at my phone right now so it’s hard to provide an simple example, sorry. I can help you with that later if you have issues.

#3

So I would have to manually request each time period? Is there a way to just group by date automatically? I’m thinking in terms of merge_events_by_keys but instead of merging by keys I would merge by date. Is that possible?

#4

You have to specify each timeperiod yes, but it can be done in a single request as the timeperiod field takes an array of timeperiods. The plot of non-afk time for days/weeks/months/years in the web-ui works this way. Here’s a direct copy of the request from the web-ui

{
  "query":[
    "afkbucket = \"aw-watcher-afk_johan-laptop2\";",
    "not_afk = flood(query_bucket(afkbucket));",
    "not_afk = merge_events_by_keys(not_afk, [\"status\"]);",
    "RETURN = not_afk;"
  ],
  "timeperiods":[
    "2020-05-14T04:00:00+02:00/2020-05-15T04:00:00+02:00",
    "2020-05-15T04:00:00+02:00/2020-05-16T04:00:00+02:00",
    "2020-05-16T04:00:00+02:00/2020-05-17T04:00:00+02:00",
    "2020-05-17T04:00:00+02:00/2020-05-18T04:00:00+02:00",
    "2020-05-18T04:00:00+02:00/2020-05-19T04:00:00+02:00",
    "2020-05-19T04:00:00+02:00/2020-05-20T04:00:00+02:00",
    "2020-05-20T04:00:00+02:00/2020-05-21T04:00:00+02:00",
    "2020-05-21T04:00:00+02:00/2020-05-22T04:00:00+02:00",
    "2020-05-22T04:00:00+02:00/2020-05-23T04:00:00+02:00",
    "2020-05-23T04:00:00+02:00/2020-05-24T04:00:00+02:00",
    "2020-05-24T04:00:00+02:00/2020-05-25T04:00:00+02:00",
    "2020-05-25T04:00:00+02:00/2020-05-26T04:00:00+02:00",
    "2020-05-26T04:00:00+02:00/2020-05-27T04:00:00+02:00",
    "2020-05-27T04:00:00+02:00/2020-05-28T04:00:00+02:00",
    "2020-05-28T04:00:00+02:00/2020-05-29T04:00:00+02:00",
    "2020-05-29T04:00:00+02:00/2020-05-30T04:00:00+02:00",
    "2020-05-30T04:00:00+02:00/2020-05-31T04:00:00+02:00",
    "2020-05-31T04:00:00+02:00/2020-06-01T04:00:00+02:00",
    "2020-06-01T04:00:00+02:00/2020-06-02T04:00:00+02:00",
    "2020-06-02T04:00:00+02:00/2020-06-03T04:00:00+02:00",
    "2020-06-03T04:00:00+02:00/2020-06-04T04:00:00+02:00",
    "2020-06-04T04:00:00+02:00/2020-06-05T04:00:00+02:00",
    "2020-06-05T04:00:00+02:00/2020-06-06T04:00:00+02:00",
    "2020-06-06T04:00:00+02:00/2020-06-07T04:00:00+02:00",
    "2020-06-07T04:00:00+02:00/2020-06-08T04:00:00+02:00",
    "2020-06-08T04:00:00+02:00/2020-06-09T04:00:00+02:00",
    "2020-06-09T04:00:00+02:00/2020-06-10T04:00:00+02:00",
    "2020-06-10T04:00:00+02:00/2020-06-11T04:00:00+02:00",
    "2020-06-11T04:00:00+02:00/2020-06-12T04:00:00+02:00",
    "2020-06-12T04:00:00+02:00/2020-06-13T04:00:00+02:00",
    "2020-06-13T04:00:00+02:00/2020-06-14T04:00:00+02:00"
  ]
}

If I understand the question this works the same way, the difference is just that it’s not in the query language itself but rather in the request which contains the query.
We are trying to avoid having date handling in the query language because we want it to be focused on only transforming data, we’d prefer to keep date management out of it.
It’s also a good thing because that means that the client can decide timezone so aw-server doesn’t have to be aware of that.

#5

You’ve understood the question correctly, thanks for the explanation. I’m a bit confused about the query from the web-ui though. I can’t just paste that into the Query Explorer, how do I use that? What function takes timeperiods? The only function I see is query2 but there’s no documentation with that.

#6

Oh sorry, I was not aware that you were using the Query Explorer.
Well, the query explorer only supports setting a single timeperiod (based on the “start” and “end” date selectors on the top).
Then yes you are right it is not possible to do today with that tool, you can only select a single timeperiod (the “start” and “end” date selectors) at a time.
You would have to do your own HTTP request to accomplish this, it is not too tricky though if you don’t find fancy visualizations to be necessary.

It would be cool however if we could add something like you suggested to the primary UI, a way to more easily see trends of how long you do something specific rather than just “being at the computer” which today is the only metric for longer periods of time. Having such computations for more than a week though might be slow.

#7

Gotcha, thanks for the clarification. If I were to do my own HTTP request is that

how I would format it?

I’ve been thinking a bit recently about what additional kinds of analysis & features would best augment the data already collected by activity watch. Would be very useful to see this feature implemented :grin:

#8

I’ve been thinking of implementing this for a long time. It’s definitely near the top of the list of thing to build into the web UI.

#9

You’d do something like curl localhost:5600/api/0/query -x POST -d '{the JSON}'.

That’s from memory though, so could be slightly wrong.

Edit: It was slightly wrong, here’s a working example:

curl localhost:5600/api/0/query/ \
    -X POST \
    -H 'Content-Type: application/json' \
    -d '{"timeperiods": ["2020-05-24/2020-05-25"], "query": ["RETURN = query_bucket(\'aw-watcher-afk_erb-main2-arch\')"]}'
#10

Pretty much yes

I made a quick prototype with the application postman based on your query, the visualization is ugly but it works. If you want more dates you just have to add more timeperiods into the request (which could also be automated of course). The regex in this example only looks for “firefox” but you can set it to whatever you want.

Screenshot%20from%202020-05-30%2008-50-27
Screenshot from 2020-05-30 08-50-27.png2560×1440 251 KB

To do this yourself you need to set postman to POST on the URL localhost:5600/api/0/query and then put the following in the body tab

{
    "query": [
	"window_events = query_bucket(find_bucket(\"aw-watcher-window_\"));",
	"afk_events = query_bucket(find_bucket(\"aw-watcher-afk_\"));",
	"window_events_active = filter_period_intersect(window_events, filter_keyvals(afk_events, \"status\", [\"not-afk\"]));",
	"events = filter_keyvals_regex(window_events, \"app\", \"firefox\");",
	"RETURN = {\"period\": TIMEINTERVAL, \"duration\": sum_durations(events)};"
    ],
    "timeperiods": [
	"2020-04-01T00:00:00+02:00/2020-05-01T00:00:00+02:00",
	"2020-05-01T00:00:00+02:00/2020-06-01T00:00:00+02:00"
    ]
}

And if you want the very ugly table visualization you can put the following in the tests tab

var template = `
    <table bgcolor="#FFFFFF">
        <tr>
            <th>Date</th>
            <th>Seconds</th>
        </tr>

        {{#each timeperiods}}
        <tr>
            <td>
            {{period}}
            </td>
            <td>
            {{duration}}
            </td>
        </tr>
        {{/each}}
    </table>
`;

pm.visualizer.set(template, {
    timeperiods: pm.response.json()
})