locked
System.TimeStamp in query returns epoch value instead of message timestamp since oct 6? RRS feed

  • Question

  • We use stream analytics to output event hubs messages to azure storage. We use a time based partition/rowkey setup to partition the messages in relation to a set reference date in the following manner:

    CAST(DATEDIFF (hour, DATETIMEFROMPARTS (year, month, day, 0, 0, 0, 0), System.TimeStampasNVARCHAR(MAX)) 

    Around Oct 6 2pm CEST this suddenly appears to no longer function properly, as the calculation returns a negative number, even though we changed nothing and the reference date is in the past. I downloaded some sample data and used it to test our query and noticed this incorrect output, which results in the output not being written to the table storage due to invalid characters in the partition key. 

    The only thing I can think of why this no longer works is that the System.TimeStamp is incorrect on the analytics nodes and somehow is further in the past than our reference date, but how would that be possible and can we even fix this? Edit: I simply performed a test using SELECT System.TimeStamp on actual data and it outputs the epoch timestamp instead of taking the enqueue time of the message..

    Also, we're seeing relatively high amounts of data conversion and server errors (i.e. non zero) but sampling apparently offending data and testing it against the query works just fine.






    Monday, October 9, 2017 3:05 PM

All replies

  • Ok so for now I have been able to 'fix' the timestamp issue by explicitly using EventEnqueuedUtcTime instead of System.TimeStamp (which it was doing automatically before, and which it is supposed to do according to the documentation).

    "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." 

    Its still rather weird that this functionality apparently changed out of nowhere.

    Tuesday, October 10, 2017 7:15 AM