{
"Key": "Dryer_Sensor",
"State": "0.8",
"Timestamp": "1464782405968",
"Site": "Bir57",
"EventProcessedUtcTime": "2016-06-01T13:24:49.4517020Z",
"PartitionId": 0,
"EventEnqueuedUtcTime": "2016-06-01T12:00:06.4520000Z",
"IoTHub": {
"MessageId": "7e6d0379d85046f295c0cdaeaabe25d8",
"CorrelationId": null,
"ConnectionDeviceId": "openhab",
"ConnectionDeviceGenerationId": "635957210185596241",
"EnqueuedTime": "0001-01-01T00:00:00.0000000",
"StreamId": null
}
}query: DATEADD(millisecond, CAST([Timestamp] as bigint), '1970-01-01T00:00:00Z') as timeFromString
this will result in '1970-01-03T07:09:18.032Z' which is wrong!
you can correct this by modifying the input so its strips the quotes from the epoch
{
"Key": "Dryer_Sensor",
"State": "0.8",
"Timestamp": "1464782405968",
"epochtime": 1464782405968,
"Site": "Bir57",
"EventProcessedUtcTime": "2016-06-01T13:24:49.4517020Z",
"PartitionId": 0,
"EventEnqueuedUtcTime": "2016-06-01T12:00:06.4520000Z",
"IoTHub": {
"MessageId": "7e6d0379d85046f295c0cdaeaabe25d8",
"CorrelationId": null,
"ConnectionDeviceId": "openhab",
"ConnectionDeviceGenerationId": "635957210185596241",
"EnqueuedTime": "0001-01-01T00:00:00.0000000",
"StreamId": null
}
}and modifying the query so it omits the cast (where the bug resides): DATEADD(millisecond, epochtime, '1970-01-01T00:00:00Z') as time
this will result in the expected result: 2016-06-01T12:00:05.968Z
