Aggregation LAST yielding unexpected result

Help
1

I’m running a timeseries query from a Losant API node. Here is the query schema I’m using:

{
	"aggregation": "LAST",
  "limit": 1,
	"attributes": [
    "CLIENT"
	],
	"deviceIds": [
    "{{data.this_test.value.deviceId}}"
	],
	"start":1,
  "end":{{data.this_test.value.end_time_ms}}
}

This query responds with a data-point on October 4, 2019. However, if I look at the same device’s data using Data Explorer, I can see that we have values for the CLIENT field received after October 4 but before the end-time.

So, it appears that this query is giving me a value of the CLIENT field which occurred at a mid-point between "start":1 and "end":{{data.this_test.value.end_time_ms}} rather than the latest value of CLIENT received between "start":1 and "end":{{data.this_test.value.end_time_ms}}.

One red flag I noticed is that the query responds with "aggregation": null even though I provided LAST as the aggregation type. Is there something else I need to get the LAST aggregation to give me the expected result?

3

Hi Alexander,

I just wanted to let you know that I am currently working on this, and have been able to partially replicate the behavior you describe.

Initially I noticed that the “start” value of 1 in my test gave me the first available data point for the device, which happened to exist within the data retention period of my application. Since the data point was still accessible, the device’s first value was returned by the query. You said the value that was returned was between your two time values, for testing purposes, what is your “end” value usually set to?

I have not yet determined why I am given the first value and not the last, and why the aggregation on the payload is set to “null,” but I am working through this now and will get back to you shortly.

Thanks!
Julia

4

Thank you very much. Good to know I wasn’t imagining things.

for testing purposes, what is your “end” value usually set to?

I had been setting “end” to a value corresponding to November 18th.

5

Hi @Alexander_Farley,

I apologize for the delay, I had to check with our engineering team for clarification on some behavior.

In the coming days, I am going to update the node and schema documentation for Time Series Queries. There are many possible combinations of variables within the Time Series request that produce varying results, and the documentation is lacking around this action. Once I have updated this documentation, I will link it below :smile:

I want to break down a couple of things that will help explain the results we both received.

Buckets

I was made aware of a crucial detail for this query: the data is aggregated into buckets. Thus, aggregation is done on the data in “pieces” at the length of the resolution. For example, if I were to set aggregation to “LAST” and my resolution to 60000 milliseconds (60 seconds), the value printed to my payload would really be the last value reported by the device within that minute. This aggregation would be done for every minute between my start and end values, and the value would be the LAST value within that minute.

This can get very tricky when seeing the associated timestamp for the bucket. The timestamp associated with the returned value is the timestamp of the first aggregated value in the bucket, while the returned value is the data point you are looking for, LAST.

Resolution

The first issue I saw was what you described, the query schema you provided returned both the aggregation and resolution as null. This is occurring as either resolution is required unless the aggregation is set to “NONE”. If you were to set your limit to a number greater than one, the difference of timestamps would indicate the resolution for each bucket.

Limit and Timestamps

When I set my limit to 1, I received the first existing data point from my device… or so I thought. Since the query uses buckets and I had set my resolution to one hour, the timestamp on the returned value was for my very first data point. However, the associated value truly was the LAST value within that hour. The timestamp correlated to the beginning, or first value, of the aggregated bucket, but I believed I was receiving the ever data point.

Solution

The query I used is by no means the only option, I just wanted to keep it similar to your configuration. Feel free to change it up if you are looking for something different.

{
   "aggregation": "NONE", // every data point between start and end //
    "limit": 1, // return only one value
	"attributes": [
    "temperature"
	],
	"deviceIds": [
    "{{data.deviceId}}"
	],
    "order":"desc", // put in descending order, giving the last value first
	"start":1,
    "end":{{data.end_time_ms}}
}

Again, I will update you once I have improved the documentation. Please let me know if you run into another issue with this or have a question, as it is a confusing query to configure without detailed information.

Thanks so much!
Julia

6

Thank you, I think that explains the behavior I saw. I will try the query you suggested.

1 Like