Ingesting IoT Data to Timestream with Timestamp

Question

I wish to ingest data to Timestream with a custom timestamp from IoT. These timestamps will often be in the past -- but well within the retention period. Here are a couple sample messages:

{
      "time": "2020-09-03T17:50:07.790000",
      "Battery Cranking Voltage": "12.035839999999999"
    }

{
      "time": "2020-09-03T17:42:28.770000",
      "Electric Energy Out": "6798.6"
    }

Note the time property is important and related to the measurement. Also note that the measurement changes with each message.  AND there are a large and flexible number of measurements.

Setting an IoT Rule/Action to send this to Timestream will succeed in creating measure_names and _values, but with the WRONG timestamp.  The records are stamped with the INGEST time not the value from the time field.

I can "fix" that by changing the NAME of the 'time' property to, say 'timestamp,' and parsing that field with `time_to_epoch`.  BUT, that will create extraneous records in Timestream where the measure_name is 'timestamp' and the value is the same as the time.

So I'm left with a choice between doubling the size of my database or not having useful timestamps.

Is there another way?

---- EDIT ----

To be more detailed, here is another run:

example messages published on topic `vt/cvra/teleTester` 
`
{
  "timestamp": "2020-10-01 19:50:36.050",
  "Road Speed": "2.0"
}

{
  "timestamp": "2020-10-01 19:50:34.147",
  "Gear Position": "3.0"
}
`

IoT Rule SQL:
`SELECT * FROM 'vt/cvra/+/cardata'`

TImestamp `value` field:
`${time_to_epoch(timestamp, "yyyy-MM-dd HH:mm:ss.SSS")}`
Units : `MILLISECONDS`

Results in tImestream:

device_id   measure_value::varchar     measure_name      time
```
teleTester	2020-10-01 19:50:36.050	timestamp	2020-10-01 19:50:36.050000000
teleTester	2.0	Road Speed	2020-10-01 19:50:36.050000000
teleTester	2020-10-01 19:50:35.050	timestamp	2020-10-01 19:50:35.050000000
teleTester	0.0	Road Speed	2020-10-01 19:50:35.050000000
teleTester	3.0	Gear Position	2020-10-01 19:50:34.147000000
teleTester	2020-10-01 19:50:34.147	timestamp	2020-10-01 19:50:34.147000000
```

See how there are extra rows with the measure of `timestamp`?

I'm looking to suppress that.

Accepted Answer

I did manage to work around this issue.  You simply cannot pass time in the payload.  You can use a timestamp from the payload, but it will creat unnecessary storage by also adding the property as a measure. I worked around it by passing epoch millis on the topic and extracting the timestamp from there. See http://iot.awsworkshops.com/amazon-timestream/lab131a-iotandtimestream/

Ingesting IoT Data to Timestream with Timestamp

Relevanter Inhalt