Get difference between two dates in seconds using Time Series Expression Syntax in Azure Time Series Insights Explorer

Question

I have an Event Hub that sends data to Time Series Insights, with the following message format:

{
  "deviceId" : "Device1",
  "time" : "2022-03-30T21:27:29Z"
}

I want to calculate the difference in seconds between the Event Hub EnqueuedTimeUtc property and time property.

I created a Time Series Insights with an Event Source without specifying the Timestamp property name, in that way in Time Series Insights our Timestamp ($ts) property will be the EnqueuedTimeUtc property of the Event.

Now with those two properties, using TSX (Time Series Expression Language), I want to do something like this:

$event.$ts - $event.time.DateTime

The problem I'm facing is that the result of that operation returns a DateTime, but in Time Series Expression there isn't a function to convert DateTime to Seconds, or to Unix Timestamp. Time Series Expresion Doc

Is there a way of achieving this using Time Series Insights and TSX (Time Series Expression)?

Thanks!

Answer 1

TSI is an depreciated service in Azure and there are not much features (inbuilt functions) available in it to explore data. Therefore, I suggest you to use Azure Data Explorer to work with the Event Hub Data.

Azure Data Explorer provides inbuild datetime_diff function which allows to calculate the period in many supported formats based on your requirement using simple Kusto Query Language.

datetime_diff(): Calculates calendarian difference between two datetime values.

Syntax:

datetime_diff(period,datetime_1,datetime_2)

Example: second = datetime_diff('second',datetime(2017-10-30 23:00:10.100),datetime(2017-10-30 23:00:00.900))