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.