Get difference between two dates in seconds using Time Series Expression Syntax in Azure Time Series-CodePudding

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.

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

Thanks!

CodePudding user response：

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))