Spilt timestamp into separate columns in Stream Analytics for further filtering in Power BI

Question

Currently am i receiving data from sensors (via Event Hub) to Stream Analytics i JSON format. I want to visualize these data in Power BI and would like to filter the events to specific days, month, year etc. But for to be able to do this, I need to split the timestamp into new columns. I am receiving the timestamp in ISO 8601 format:

"timestamp": "2020-01-13T08:24:50.0749840Z"

I have not been able to split this kind of timestamp in Stream analytics. Is there anyone that has done this before?

Answer 1

Please just use built-in Date and Time Functions in ASA which could identify format of your date data.

sql:

SELECT
     Year(input.timestamp) as year,
     Month(input.timestamp) as month,
     Day(input.timestamp) as day
FROM
      input

Output: