Search code examples
azureazure-stream-analytics

How to get system time while running Azure Stream Analytics Query

In the application, there are set of events that come to event hub and as part of stream analytics, I have set of requirements and one of them is to find out if certain set of records havent arrived at event hub in last x minutes. I have a timestamp inside the message that tells me the effective time of the message, but in order to compute the lag (between the effective time of the message and now()), I need to know the current timestamp when I am running the query.

I tried System.Timestamp, but it gives me the value "1970-01-01T12:01:01.0010000Z"

The Enqueue timestamp for the message is latest like "2018-05-06T00:00:00.1000000Z"... But not sure why System.timestamp does not return me the enqueue time (when I have not used "Timestamp By" Clause).

So, I have 2 questions:

  1. How do I get current timestamp of the server when the stream analytics query is getting executed.
  2. Why system.timestamp does not return the enqueue timestamp

Query: SELECT System.Timestamp as ts FROM source

Result: "1970-01-01T12:01:01.0010000Z"

None of the timestamp in the input data is older than 6th May 2018.

Thanks, Rajneesh

Solution

  • The Enqueue timestamp for the message is latest like "2018-05-06T00:00:00.1000000Z"... But not sure why System.timestamp does not return me the enqueue time (when I have not used "Timestamp By" Clause).

    As System.Timestamp (Stream Analytics) states as follows:

    If a TIMESTAMP BY clause is not specified for a given input, arrival time of the event is used as a timestamp. For example Enqueued time of the event will be used in case of Event Hub input.

    I just tested this issue and found that if you just test the query under "JOB TOPOLOGY > Query" of Azure Portal, then for the approach not specifying the TIMESTAMP BY clause, the value of System.Timestamp would be 1970-01-01T12:01:01.0010000Z. While specifying the TIMESTAMP BY clause, the value of System.Timestamp would be column value you specified in the TIMESTAMP BY clause.

    Then, I just created a Blob input and Blob outout for my job, then under Overview tab, click Start to run my Stream Analytics job, I could successfully retrieve the correctly value for System.Timestamp.

    Query:

    SELECT birth,name,System.Timestamp as t   
into output
FROM input

    TEST:

    enter image description here

    UPDATE:

    How do I get current timestamp of the server when the stream analytics query is getting executed.

    For your requirement, I assume that you could use JavaScript UDF. I just created a sample UDF as follows:

    function main(s) {
    return new Date().toISOString();
}

    TEST:

    enter image description here

    You could compute the lag within a UDF or leverage the built-in functions Date and Time Functions.