Query Pattern of the Week: Use expressions inside a TIMESTAMP BY clause


Azure Stream Analytics allows expressing complex event processing rules using a simple SQL-like query language. Given the temporal nature of Stream Analytics queries, it is important to specify a timestamp for every input event.  By default, Stream Analytics will use arrival time of the input event – e.g. if Event Hub is used as an input, the timestamp will be the time when the event was received by the Event Hub.

For many streaming applications it is important to use the exact time when an event occurred. For these cases, the TIMESTAMP BY clause can be used to specify custom timestamp.  For example, if we want to use column “time” as event timestamp, the query will look like:

SELECT *

FROM input TIMESTAMP BY [time]

Until recently, you could only use field names within the TIMESTAMP BY clause. Some customers have asked for more flexibility – for example one may want to adjust time from local time to UTC, specify conversion from different time format (like UNIX epoch time), etc.  

With the most recent update of Stream Analytics, you can use any expression of type DATETIME within the TIMESTAMP BY clause.

Example: Imagine we receive data from two different type of sensors. One type of sensor uses “time” as the name of the timestamp field, while the second type of sensor is using the “readtime” field.

Input:

sensorid

value

time

readtime

s1

70

2015-09-29T17:48:42

 

s2

55

 

2015-09-29T17:48:49

s1

78

2015-09-29T17:48:53

 

s2

45

 

2015-09-29T17:48:57

s1

82

2015-09-29T17:49:02

 

s1

73

2015-09-29T17:49:07

 

s2

51

 

2015-09-29T17:49:11

s2

48

 

2015-09-29T17:49:15

 

Test File: Download from GitHub: DifferentTimestamps.json

Query:

SELECT [sensorid], AVG([value])

FROM input TIMESTAMP BY

    CASE WHEN [time] is NOT NULL

        THEN [time]

        ELSE [readTime]

        END     

GROUP BY [sensorid], TUMBLINGWINDOW(hour, 1)

Output:

sensorid

avg

s1

75.75

S2

49.75

Explanation:

By adding an expression inside TIMESTAMP BY clause, we can use the same query to handle both input formats and produce average values for both types of sensors:

Looking for more examples?

To learn about more patterns, check out the Common Stream Analytics Query Patterns page.  Looking to solve a problem that you don’t see captured yet?  Let us know via our msdn forum or by tweeting us@AzureStreaming.

Comments (0)

Skip to main content