Aggregation on timestamps

Question

I am trying to query time series insights and i want to calculate difference between max and min time stamp of my events. My json request body is this, but this doesnt seem to work. I am getting null values in my difference variable. Can someone please help?

{
    "aggregateSeries": {
        "timeSeriesId": [
            "LPN102"
        ],
        "searchSpan": {
            "from": "2020-12-22T14:30:00Z",
            "to": "2020-12-23T14:50:00Z"
        },
        "interval": "PT1M",
        "inlineVariables": {
            "difference": {
                "kind": "aggregate",
                "filter": null,
                "aggregation": {
                    "tsx": "max($event.$ts) - min($event.$ts)"
                }
            }
        },
        "projectedVariables": [
            "difference"
        ]
    }
}

Answer 1

@Purohit, Aahana , @António Sérgio Azevedo ,

All though the expression "tsx": "max($event.$ts) - min($event.$ts)" is a valid expression, the data type of the result turns out to the "TimeSpan". TimeSpan data type is not a supported type as of today and is the reason for empty results.
We are working on an improvement that informs the user with appropriate message. Hope this helps.

Answer 2

Hi @Purohit, Aahana ,

• Can you please confirm that you have a value for each minute PT1M on the specified searchSpan?

Also note the following behavior explained here:

"For a specified search span and interval, this API returns an aggregated response per interval per variable for a Time Series ID. The number of intervals in the response dataset is calculated by counting epoch ticks (the number of milliseconds that have elapsed since Unix epoch - Jan 1st, 1970) and dividing the ticks by the interval span size specified in the query. The timestamps returned in the response set are of the left interval boundaries, not of the sampled events from the interval."

Other related threads and feedback fyi:
1 - Stack Overflow - Azure TSI aggregateSeries query returns data with inconsistent plotting
2 - Feedback - Fix search span and interval misalignment issue in AggregateSeries queries

Remember:

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Answer 3

Hey @Purohit, Aahana !

It seems this TSX in your aggregation clause - "max($event.$ts) - min($event.$ts)" - may be the issue. Subtracting timestamps would give you a Timespan data type which is not currently supported. See the supported data types here.

Additionally, to answer your question in the above comments about interval calculations, here's an example:

The green shows the query's search span, the orange shows interval size and the blue shows the intervals that TSI generates starting at Unix epoch. Now it may happen that the calculated intervals don't align with the search span chosen. In this case, we'd take the interval in which your search span begins as the first bucket and will take the interval in which your search span ends as the last bucket and perform the aggregations.

Each interval considers data including the left boundary up until the right boundary. The timestamps that are returned by the query are of the left interval boundary. In this example, T1, the first timestamp returned, occurs before the "from" time on the search span. This may look strange, but we do not consider any events that fall outside of the search span. So the first two points and the last two will not be included in the aggregation for T1 and T5, respectively. If we don’t find data, like for the interval T3 and T4, we return null for that interval. From this image, the query would return 5 timestamps and 3 values (value, value, null, null, value).

So, if we take your example in the comments above, we see no data for "2021-01-05T13:41:25Z" and value X at "2021-01-05T13:41:26Z". The interval starting at "2021-01-05T13:41:24Z" would return null (similar to interval T4 in the example). The next interval starting at "2021-01-05T13:41:26Z" would include the value X in it's aggregation.

I hope that helps, please let me know if you have more questions!!