locked
Azure Stream Analytics has function like GETDATE() or CURRENT_TIMESTAMP? RRS feed

  • Question

  • My scenarios is that sensors always store datas at local,sometime these datas will be uploaded to Event Hub but I don't need all the data,I need filter data by a time field in Azure Stream Analytics.what should I do?

    SELECT
        devId,
        dataType,
        utc
    INTO
        into
    FROM
        output
    WHERE
        (TRY_CAST(utc AS bigint) IS NOT NULL) AND
        (DATEADD(millisecond, utc, '1970/01/01 GMT') >= DATEADD(minute,-5, CURRENT_TIMESTAMP))

    Wednesday, May 11, 2016 8:20 AM

Answers

  • Azure Stream Analytics does not expose these functions. in many streaming scenarios it is better to avoid such functions as their values are not repeatable - e.g. next time you start the job and point to the same data, it will produce different results.

    What is the scenario are you trying to solve? Looking at the query, it seems that you want to discard events if they are arriving 5 minutes late. This can be achieved by using Late Arrival Policy in Azure Stream Analytics. Go to "Configure" tab in ASA job settings in Azure Portal and set Late Arrival tolerance to 5 minutes and "Drop" as  a policy.

    You also need to use TIMESTAMP BY clause in your query to specify how to compute timestamp  of the events. 

    SELECT   

    devId,

        dataType,

        utc INTO    output FROM    input TIMESTAMP BY DATEADD(millisecond, utc, '1970/01/01 GMT')


    Thursday, May 26, 2016 6:52 AM