Azure Event Stream GROUP BY no code component in event stream incorrect type on timestamp with MAX

Question

In an event stream when applying a NO CODE group by component adding the field "time" and with MAX() it results in a String field rather than the original timestamp.

Answer 1

Thanks for the fast responses.

The ['time'] field comes as timestamp. MAX results in a string.
My previous question also relate to this particular field (coming from the sample stock-data) (see: https://learn.microsoft.com/en-us/answers/questions/6052223/kql-schema-inference-bug-stock-data-sample-time-fi)

It is not a business/technical meaningful use case other than exploring azure event stream query possibilities. I was trying to construct join and wanted to join on the time field using its original datetime type value.

The join does not accept "SqlCode" (custom SQL code component) inputs.

Sure casting it to a number and get the MAX and join on number would work.

Thanks for the help

Answer 2

Thanks for the fast responses.

The ['time'] field comes as timestamp. MAX results in a string.
My previous question also relate to this particular field (coming from the sample stock-data)
(see: https://learn.microsoft.com/en-us/answers/questions/6052223/kql-schema-inference-bug-stock-data-sample-time-fi)

It is not a meaningful business or technical use case other than exploring azure event stream query possibilities.
I was trying to construct join and wanted to join on the time field using its original datetime type value.
The join does not accept "SqlCode" (custom SQL code component) inputs.
Sure casting it to a number and get the MAX and join on number would work.

Thanks for the help

Answer 3

Thanks for the fast responses.

The ['time'] field comes as timestamp. MAX results in a string.
My previous question also relate to this particular field (coming from the sample stock-data)
(see: https://learn.microsoft.com/en-us/answers/questions/6052223/kql-schema-inference-bug-stock-data-sample-time-fi)

It is not a meaningful business or technical use case other than exploring azure event stream query possibilities.
I was trying to construct join and wanted to join on the time field using its original datetime type value.
The join does not accept "SqlCode" (custom SQL code component) inputs.
Sure casting it to a number and get the MAX and join on number would work.

Thanks for the help

Answer 4

Likely cause: your event field used in MAX() is still treated as string at the aggregation input, so MAX() returns lexicographic ordering instead of datetime semantics.

1. Add a schema-normalization step immediately before Group by. In Eventstream, use Manage fields (or SQL operator) and convert the timestamp field to datetime before aggregation.
2. Validate the type in preview before publishing. Confirm the transformed field is datetime in upstream preview.
3. If no-code transformation still outputs string, switch to SQL operator and cast explicitly: SELECT DeviceId, MAX(TRY_CAST([time] AS datetime)) AS MaxTime FROM input GROUP BY DeviceId, TumblingWindow(minute, 5).
4. TIMESTAMP BY controls event time, but your payload field used in MAX() must still be datetime-typed.
5. Validate that MaxTime is datetime and investigate nulls from TRY_CAST as malformed source values.

Confirmed vs possible causes:

• Confirmed: MAX() returns the same type family as its input expression.
• Possible: schema inference still treats time as string.

References:

• https://learn.microsoft.com/stream-analytics-query/max-azure-stream-analytics
• https://learn.microsoft.com/stream-analytics-query/group-by-azure-stream-analytics
• https://learn.microsoft.com/stream-analytics-query/timestamp-by-azure-stream-analytics
• https://learn.microsoft.com/fabric/real-time-intelligence/event-streams/process-events-using-sql-code-editor

“If you find this answer helpful, please upvote it. If this resolves your issue, please mark it as the accepted answer so that it can help others in the community.”

Answer 5

Insert a Manage fields transformation before Group by and convert time to datetime, because MAX() returns the same data type as its input expression. ^1^

No-code correction

1. Open the eventstream and select Edit.
2. On the connection immediately before Group by, select Transform events or add destination → Manage fields.
3. Open the transformation with the pencil icon.
4. Add the existing time field and change its data type from string to datetime. Manage fields supports changing a field’s data type. ^2^
5. Save the transformation and inspect its preview:
   • time should now appear as a datetime.
   • Invalid timestamp text should be corrected at the source or normalized before aggregation.
6. Connect Manage fields to Group by.
7. In Group by, configure:
   • The required grouping dimensions.
   • The required time window.
   • MAX(time) under aggregations, with an output name such as MaxTime.
8. Preview the Group by output and confirm MaxTime is datetime before publishing.

Do not also place time in the list of grouping dimensions unless events with identical timestamps are intentionally meant to form separate groups. Group By returns one summary row for each combination of grouping values and window. ^3^

TIMESTAMP BY accepting ISO 8601 strings does not mean that every reference to that payload field becomes datetime. It controls the event’s application timestamp; MAX(time) still operates on the payload field’s inferred or declared type. ^4^

SQL operator fallback

If the no-code preview already identifies the input as datetime but Group by still emits a string, replace that transformation with a SQL operator and cast explicitly. Add it through Transform events → SQL, then test before saving. SQL operators use Azure Stream Analytics query semantics. ^5^

SELECT
    DeviceId,
    MAX(TRY_CAST([time] AS DATETIME)) AS MaxTime
INTO
    output
FROM
    input
GROUP BY
    DeviceId,
    TumblingWindow(minute, 5)

Replace DeviceId, the window, and the input/output aliases with those from the eventstream. Use TRY_CAST while diagnosing malformed values; values that cannot be converted become null rather than failing the entire conversion.

After selecting Test query, verify that:

• MaxTime is datetime.
• The expected records contribute to each window.
• No unexpected nulls indicate malformed timestamp strings.

If the source preview, Manage fields output, and Group by input all show datetime but only the Group by output changes to string, capture those three schema previews and the transformation configuration when opening a Fabric support case. That would contradict the documented MAX() return-type behavior and is not resolved by further source-side conversion.

---

References

1. MAX
2. Route data streams based on content in Microsoft Fabric eventstreams
3. GROUP BY
4. TIMESTAMP BY
5. Process events by using a SQL operator