Rest sensor - strange date format and sum only values of identical attributes

[1mfaasj (Bowa)]

Hi, I’m trying to make sense to a rest sensor in Home Assistant and got 2 questions.

1. The api returns a quite specific date format, does anyone know which kind this is and how to convert it into something more readable? For instance:

{
    "d": [
        {
            "__type": "QueryStats:#Microsoft.Bing.Webmaster.Api",
            "AvgClickPosition": -1,
            "AvgImpressionPosition": 6,
            "Clicks": 0,
            "Date": "/Date(1714114800000-0700)/",
            "Impressions": 1,
            "Query": "my beautiful keyword"
        },

2. the api returns a list which may contain the same “query”. so for instance
   "Query": "my beautiful keyword"
   could be listed twice, like this:

  - __type: QueryStats:#Microsoft.Bing.Webmaster.Api
    AvgClickPosition: -1
    AvgImpressionPosition: 7
    Clicks: 0
    Date: /Date(1717138800000-0700)/
    Impressions: 1
    Query: my beautiful keyword
  - __type: QueryStats:#Microsoft.Bing.Webmaster.Api
    AvgClickPosition: -1
    AvgImpressionPosition: 6
    Clicks: 0
    Date: /Date(1717138800000-0700)/
    Impressions: 1
    Query: another beautiful something
  - __type: QueryStats:#Microsoft.Bing.Webmaster.Api
    AvgClickPosition: -1
    AvgImpressionPosition: 6
    Clicks: 0
    Date: /Date(1717743600000-0700)/
    Impressions: 1
    Query: my beautiful keyword

I need to sum the “impressions” of each identical query.
so in my example I’m expecting 2 impressions because the query is listed twice and when I sum those impression values, it’s a total of 2.

Is this even possible with HA? I know how to sum ALL impressions but not how to sum only the identical ones.

any help is appriciated.

[vingerha]

The date seems to be a timestamp, they are all at 07:00 GMT
What the -0700 does no clue, maybe compensate to the time and set it back to midnight or is it the time representation of the timestamp? Either way, you can convert timestamp to date (numerous examples)
Then, adding two entries based on the key ‘Query’… probably you need to run via jq

[Troon]

@1mfaasj

/Date(1714114800000-0700)/

That looks like a Unix epoch timestamp in milliseconds, with the timezone appended, though to be sure, we’d need to know what it’s meant to represent. (edit: this suggest the same: IWebmasterApi.GetQueryStats(String) Method (Microsoft.Bing.Webmaster.Api.Interfaces) | Microsoft Learn)

Using my guess:

{% set input = '/Date(1714114800000-0700)/' %}
{% set ts,tz = (input|regex_findall('\(([\d-]*)\)')|first).split('-') %}
{{ (ts|int / 1000)|as_datetime - timedelta(hours=tz|int/100) }}

@1mfaasj

I need to sum the “impressions” of each identical query.

If that YAML list is value_json['d'] from your first code snippet, then:

{% set ns = namespace(d={}) %}
{% for q in value_json['d']|map(attribute='Query') %}
{% set ns.d = dict(ns.d, **{q:ns.d.get(q,0)+1}) %}
{% endfor %}
{{ ns.d }}

returns:

{
  "my beautiful keyword": 2,
  "another beautiful something": 1
}

Click to expand explanation of the template

First we set up a namespace, which is an object that allows variables to persist at different scope levels. Without this, the change inside the for would be lost. We define an empty dictionary called ns.d.

Next, we look at each query in turn. value_json['d']|map(attribute='Query') returns a simple list of all the Query attributes from each item in the list.

Then for each query, we update the dictionary. dict() (in this usage) takes an existing dictionary and a new one (via the **kwargs parameter) and combines them.

The new dictionary is a single key:value pair made up of the current query and the value of that query count so far plus one. ns.d.get(q,0) looks up the current value of q in ns.d, and if it’s not found, returns 0. So with the data you’ve given, it goes:

1. ns.d is empty, so dict({}, **{"my beautiful keyword": 0+1})
2. now we have dict(ns.d, **{"another beautiful something": 0+1})
3. and finally: dict(ns.d, **{"my beautiful keyword": 1+1})

When the newly-added dictionary has the same key as an existing one, it overwrites the existing one.

Testing screenshot, using this tool to convert your YAML to JSON:

[1mfaasj (Bowa)]

Hi Troon wow thanks for thinking along. Is there something you cannot do?
Looks like it’s the correct date/time conversion indeed, when I compare it to the data on the Bing website itself.
The next step is to get the date conversion in a list, beside the query and impressions count.

how far I’ve come:

{% for row in state_attr('sensor.bing_api', 'd') | sort(attribute='Date') %}
{{ row['Query'] }}, {{ row.Impressions }}, {{ row.Date }}
{% endfor %}

the result is:

my beautiful keyword, 1, /Date(1714114800000-0700)/
another beautiful something, 1, /Date(1714719600000-0700)/

so I’m trying to get the date/time conversion to work at the end of both lines.

What I’ve tried so far:

{% set input =  state_attr('sensor.bing_api', 'd')[0]['Date']  %}
{% set ts,tz = (input|regex_findall('\(([\d-]*)\)')|first).split('-') %}
{% set test = (ts|int / 1000)|as_datetime - timedelta(hours=tz|int/100) %}

{% for row in state_attr('sensor.bing_api', 'd') | sort(attribute='Date') %}
{{ row['Query'] }}, {{ row.Impressions }}, {{ test }}
{% endfor %}

so I changed your example a bit, to create a new variable which I can use for all the queries.
But now I get the same date/time for every query (because I use [0] right?)…
thats not correct because they differs.

my beautiful keyword, 1, 2024-04-26 00:00:00+00:00
another beautiful something, 1, 2024-04-26 00:00:00+00:00

When I remove [0] it doesn’t work:
No first item, sequence was empty.
When I add this it gives me the same result?!
{% set input = state_attr('sensor.bing_api', 'd')['Date'] | list %}

I will look now into your answer on my second question!

[Troon]

Your test variable does not get updated in your for loop: you have set it once from the first item then just re-used that date. You want this:

{% for row in state_attr('sensor.bing_api', 'd') | sort(attribute='Date') %}
{% set ts,tz = (row['Date']|regex_findall('\(([\d-]*)\)')|first).split('-') %}
{% set test = (ts|int / 1000)|as_datetime - timedelta(hours=tz|int/100) %}
{{ row['Query'] }}, {{ row.Impressions }}, {{ test }}
{% endfor %}

[1mfaasj (Bowa)]

both are working as expected. your help is much appreciated!
also thanks for the clarification for the second question.

[1mfaasj (Bowa)]

Hm I don’t understand something, when I put the latest code snippet in a markdown card or template card, it’s listed below each other:

my beautiful keyword, 1, 2024-04-26 00:00:00+00:00
another beautiful something, 2, 2024-04-26 00:00:00+00:00

but when I try to do the same with the namespace code snippet, it always put the values next to each other (whatever I try):
{'my beautiful keyword': 2, 'another beautiful something': 2}"
Is this normal behavior for a namespace?
In the template tester it is listed below each other though, what I expect. < that’s strange

[Troon]

The “namespace snippet” is producing a dictionary object, not a string. It’s not about “behaviour for a namespace”, it’s basic templating. Your original question was:

@1mfaasj

I need to sum the “impressions” of each identical query.
so in my example I’m expecting 2 impressions because the query is listed twice and when I sum those impression values, it’s a total of 2.

…which is what it does. If you want a specific layout, then you need to tell us what that is. For example, if instead of the last line, you use:

{% for i in ns.d.keys() -%}
{{ i }}: {{ ns.d[i] }}
{% endfor %}

you’ll get a simple text layout.