I am trying to calculate the duration of a signal (DI_1) from an datalogger which is connected to azure IoT Hub and stream analytics.
The input data looks like:
DateTime Digital Input
22.04.2019 14:17:00 0
22.04.2019 14:16:00 1
22.04.2019 14:15:00 0
22.04.2019 14:14:00 0
22.04.2019 14:13:00 0
22.04.2019 14:12:00 0
22.04.2019 14:11:00 1
22.04.2019 14:10:00 1
22.04.2019 14:09:00 1
22.04.2019 14:08:00 1
22.04.2019 14:07:00 1
22.04.2019 14:06:00 0
22.04.2019 14:05:00 0
22.04.2019 14:04:00 0
22.04.2019 14:03:00 0
22.04.2019 14:02:00 1
22.04.2019 14:01:00 1
22.04.2019 14:00:00 0
And the result should looks like:
DateTime Duration
22.04.2019 14:16:00 00:01:00
22.04.2019 14:07:00 00:05:00
22.04.2019 14:01:00 00:02:00
does anyone has an idea how to implement this in azure stream analytics?
at the moment ASA output is a dataset with time and digital input (1 or 0) when the digital input changes.
DateTime Digital Input
22.04.2019 14:17:00 0
22.04.2019 14:16:00 1
22.04.2019 14:15:00 0
22.04.2019 14:14:00 1
22.04.2019 14:13:00 0
22.04.2019 14:12:00 1
realized with following code:
SELECT
PE, UID, system.timestamp AS Time,
GetArrayElement(GetArrayElement(Record,0), 3) AS DI_0
INTO
[PowerBI]
FROM
[IoTHub]
WHERE
LAG(GetArrayElement(GetArrayElement(Record,0), 3), 1)
OVER (LIMIT DURATION(minute, 10)) <> GetArrayElement(GetArrayElement(Record,0), 3)
Here I have to calculate the time difference in PowerBI. Now I want to calculate it direct in azure stream analytics to avoid calculation in PowerBI.
Thank you very much for your help!
I just solved it:
SELECT
UID,
GetArrayElement(GetArrayElement(Record,0),3) AS DI_0,
system.timestamp AS Start
FROM
[IotHub]
WHERE
LAG(GetArrayElement(GetArrayElement(Record,0), 3),1)
OVER (LIMIT DURATION(minute, 10)) <>
GetArrayElement(GetArrayElement(Record,0), 3))
SELECT
DATEDIFF (second, LAG(system.timestamp,1)
OVER (LIMIT DURATION(minute, 10)), system.timestamp) AS zeitdiff,
UID, Start, DI_0
INTO
[toPowerBI]
FROM
Startzeit
WHERE
UID = 'WISE-4012_00D0C9E43D10'