locked
System.TimeStamp documentation lacking RRS feed

  • Question

  • There is an overall lack of documentation around System.TimeStamp. The description on the Windowing page implies that the value represents the end of the window.

    http://msdn.microsoft.com/en-us/library/dn835019.aspx - The output of the window will be a single event based on the aggregate function used with a timestamp equal to the window end time.

    My testing of queries found that the System.TimeStamp value actually represented the start of the window, not the end.


    Sunday, November 23, 2014 4:27 AM

Answers

  • Rory, this is indeed a bug in our Test function!

    Once I uploaded your JSON file to a blob and ran a real job, it produced the correct-looking:

    Apologies for the test function issue --- we will get to the bottom of that shortly.  But the system itself appears to be working correctly...

    Thanks,

    --Lev


    Monday, November 24, 2014 7:58 AM

All replies

  • The code examples also indicate that System.TimeStamp is at the beginning of the window.

    http://azure.microsoft.com/nl-nl/documentation/articles/stream-analytics-get-started/

        SELECT DateAdd(second,-5,System.TimeStamp) as WinStartTime, system.TimeStamp as WinEndTime, DeviceId, Avg(Temperature) as AvgTemperature, Count(*) as EventCount 
        FROM input
        GROUP BY TumblingWindow(second, 5), DeviceId

    Sunday, November 23, 2014 4:30 AM
  • Hi Rory!

    Sorry if our documentation is not up to snuff.  Let me explain. 

    Every event flowing through Azure Stream Analytics has a timestamp.  You assign that timestamp to the raw input events by using the TIMESTAMP BY clause.  Once you start doing computations, Stream Analytics assigns timestamps to the results of those computations based on its rules, guided by the principle of "the timestamp of the result should be the time when the result was completely determined".  This principle means that:

    • When Aggregating over a window, the timestamp of the event that corresponds to a window is the end of the window.
    • When Joining two streams, the timestamp of the event that corresponds to a pair of input events is the latest one of those input events.

    The code sample you quote confirms it.  It demonstrates how to get both the start and the end of the window over which aggregation was performed.  If you said simply:

    SELECT 
        System.TimeStamp as WinEndTime, 
        DeviceId, 
        Avg(Temperature) as AvgTemperature, 
        Count(*) as EventCount 
    FROM input
    GROUP BY TumblingWindow(second, 5), DeviceId

    you would get only the end of the window, because that is what System.Timestamp represents.  So to get the beginning of the window, we need to subtract 5 seconds from the end (since the window is 5 seconds long): DateAdd(second,-5,System.TimeStamp).

    Or, putting them together, we get, as in the sample:


    SELECT 
       DateAdd(second,-5,System.TimeStamp) as WinStartTime,
       system.TimeStamp as WinEndTime, 
       DeviceId, 
       Avg(Temperature) as AvgTemperature, 
       Count(*) as EventCount 
    FROM input
    GROUP BY TumblingWindow(second, 5), DeviceId

    This is how the system is supposed to behave.   If you have found a case where System.Timestamp did not represent the end of the window, please let me know!

    Thanks,

    --Lev


    Monday, November 24, 2014 3:37 AM
  • I haven't run the data from event hubs yet. I've just been using the Test button with the Query editor. I expected that this would run the same logic however.

    Here is my scenario.

    SELECT ValidationAddressId,
        System.TimeStamp AS At,
        COUNT(*) AS ResultCount,
        SUM(CASE WHEN ResponseTimeInMilliseconds > 0 THEN 1 ELSE 0 END) AS ResponseTimeCount,
        MAX(ResponseTimeInMilliseconds) AS ResponseTimeMaximum,
        MIN(ResponseTimeInMilliseconds) AS ResponseTimeMinimum,
        SUM(ResponseTimeInMilliseconds) AS ResponseTimeTotal
    FROM [validationresults] TIMESTAMP BY ScheduledForTime
    GROUP BY ValidationAddressId,
        TumblingWindow(MINUTE, 5)

    With the following test data

    [
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:14:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":233
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:14:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":2401
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:14:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":1936
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:15:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":23065
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:15:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":633
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:15:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":31
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:16:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":2719
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:16:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":null
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:16:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":234
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:17:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":21463
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:17:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":464
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:17:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":82
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:18:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":93
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:18:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":9785
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:18:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":936
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:19:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":548
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:19:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":2665
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:19:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":3102
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20 18:20:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":null
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20 18:20:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":222
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20 18:20:00.0000000 +00:00",
        "ResponseTimeInMilliseconds":4329
      }
    ]

    The results rendered have System.TimeStamp being 10, 15, and 20 for the minute values. With this test data, this means that SystemTimeStamp is returning the beginning of the window rather than the end.


    Monday, November 24, 2014 4:58 AM
  • Hmm.  Perhaps a cut-and-paste error, but the format you use in ScheduledForTime is not supported by Stream Analytics for timestamps --- we require ISO 8601.  This means that instead of "2014-11-20 18:20:00.0000000 +00:00" you need to say "2014-11-20T18:20:00.0000000+00:00" (add 'T' and eliminate space before time zone).  Not sure why you are getting any results at all, I am getting none...

    Once I made those changes to the input data, though, the output became, as expected:

    2014-11-20T18:15:00.000Z 3
    2014-11-20T18:20:00.000Z 15
    2014-11-20T18:25:00.000Z 3

    --Lev

    Monday, November 24, 2014 5:32 AM
  • I'm definitely getting results using the Test function. My bad on the dates. I exported a query from SQL Azure to CSV, then converted to JSON for the test.

    I've updated the dates to be ISO 8601 (replaced space with T between date and time and removed space before timezone offset).

    I still get the same outcome using the Test function.


    [
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:14:00.0000000+00:00",
        "ResponseTimeInMilliseconds":233
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:14:00.0000000+00:00",
        "ResponseTimeInMilliseconds":2401
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:14:00.0000000+00:00",
        "ResponseTimeInMilliseconds":1936
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:15:00.0000000+00:00",
        "ResponseTimeInMilliseconds":23065
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:15:00.0000000+00:00",
        "ResponseTimeInMilliseconds":633
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:15:00.0000000+00:00",
        "ResponseTimeInMilliseconds":31
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:16:00.0000000+00:00",
        "ResponseTimeInMilliseconds":2719
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:16:00.0000000+00:00",
        "ResponseTimeInMilliseconds":null
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:16:00.0000000+00:00",
        "ResponseTimeInMilliseconds":234
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:17:00.0000000+00:00",
        "ResponseTimeInMilliseconds":21463
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:17:00.0000000+00:00",
        "ResponseTimeInMilliseconds":464
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:17:00.0000000+00:00",
        "ResponseTimeInMilliseconds":82
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:18:00.0000000+00:00",
        "ResponseTimeInMilliseconds":93
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:18:00.0000000+00:00",
        "ResponseTimeInMilliseconds":9785
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:18:00.0000000+00:00",
        "ResponseTimeInMilliseconds":936
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:19:00.0000000+00:00",
        "ResponseTimeInMilliseconds":548
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:19:00.0000000+00:00",
        "ResponseTimeInMilliseconds":2665
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:19:00.0000000+00:00",
        "ResponseTimeInMilliseconds":3102
      },
      {
        "ValidationAddressId":"{1038D185-D90F-45FD-A20D-56273E088E49}",
        "ScheduledForTime":"2014-11-20T18:20:00.0000000+00:00",
        "ResponseTimeInMilliseconds":null
      },
      {
        "ValidationAddressId":"{55484600-943A-4BB3-9943-B047007C5AF1}",
        "ScheduledForTime":"2014-11-20T18:20:00.0000000+00:00",
        "ResponseTimeInMilliseconds":222
      },
      {
        "ValidationAddressId":"{8651ADE7-8267-4B00-9DD5-657859C03F65}",
        "ScheduledForTime":"2014-11-20T18:20:00.0000000+00:00",
        "ResponseTimeInMilliseconds":4329
      }
    ]



    Monday, November 24, 2014 5:56 AM
  • Rory, this is indeed a bug in our Test function!

    Once I uploaded your JSON file to a blob and ran a real job, it produced the correct-looking:

    Apologies for the test function issue --- we will get to the bottom of that shortly.  But the system itself appears to be working correctly...

    Thanks,

    --Lev


    Monday, November 24, 2014 7:58 AM