Wednesday, June 01, 2016

Converting unix epoch to datetime in streaming analytics

sample input:
    {
        "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