Failed to write records to Timestream. The error received was 'Invalid timestamp value ''.

Question

I publish MQTT Messages to AWS IoT Core with the following payload:

```
{
  "SensorValue": {
    "Speed": 49,
    "Direction": 9
  },
  "MsgTime": "2024-10-10T11:05:24Z"
}
```

IoT Rule receives those messages and should insert them into the Timestream.

I defined the `timestamp value` in the following way:
```
${time_to_epoch(MsgTime,"yyyy-MM-dd'T'HH:mm:ss'Z'")}
unit='MILLISECONDS'
```

When I publish messages, I receive the following error:
```
"errorMessage": "Failed to write records to Timestream. The error received was 'Invalid timestamp value ''. Length should be in [1, 256] in UTF-8 encoded bytes.'. Message arrived on test_device, Action: timestream, Database: TSTelemetryDatabase, Table: TSTelemetryTable"
```
Any suggestions are very appreciated!

Balwanth · Answer

I tried below and it absolutely worked for me.  https://repost.aws/questions/QUmWAjLqG7RwGHCSL2tMrXvw/iot-core-rules-to-map-timestamp-to-timestream

```
Timestamp value - optional
value: ${time_to_epoch(MsgTime,"yyyy-MM-dd'T'HH:mm:ss'Z'")}
unit: MILLISECONDS
```

LMtx_IoT_Advisor_and_Developer · Answer

By default, CDK creates the IoT Rule with the SQL version `2015-10-08`, but the `time_to_epoch` requires the SQL version `2016-03-23` and later.

CDK snippet defining the SQL version:
```
        iot_rule = iot.CfnTopicRule(
            self, "IoTTelemetryRule",
            rule_name='telemetry',
            topic_rule_payload=iot.CfnTopicRule.TopicRulePayloadProperty(
                sql=sql_select,
                aws_iot_sql_version="2016-03-23",
                actions=[timestream_action, republish_action],
                error_action=error_action,
                rule_disabled=False
            )
        )
```

https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-time-to-epoch

re:Post Agent · Answer

The error you're encountering suggests that the timestamp value being passed to Timestream is invalid or empty. This could be due to a few reasons:

1. The `time_to_epoch` function might not be correctly parsing the timestamp from your MQTT message.

2. The `MsgTime` field in your MQTT payload might not be accessible in the way you're trying to use it.

To resolve this issue, try the following:

1. Ensure that the `MsgTime` field is at the top level of your JSON payload. In your current payload, it's at the root level, which is correct.

2. Modify your timestamp value expression in the IoT Rule to explicitly reference the top-level `MsgTime` field:

```
${time_to_epoch($.MsgTime, "yyyy-MM-dd'T'HH:mm:ss'Z'")}
```

The `$.` notation ensures that you're referencing the root of the JSON payload.

3. If the above doesn't work, you could try using the built-in `timestamp()` function instead, which will use the time the message was processed:

```
${timestamp()}
```

4. Double-check that the `MsgTime` field in your MQTT messages always contains a valid timestamp string.

5. Verify that your IoT Rule's SQL query is correctly selecting the fields from your MQTT payload. For example:

```sql
SELECT *, ${timestamp()} AS timestamp FROM 'your/mqtt/topic'
```

6. Ensure that the IAM role associated with your IoT Rule has the necessary permissions to write to your Timestream database and table.

If you're still encountering issues after trying these suggestions, you may want to review the full configuration of your IoT Rule and Timestream action to ensure all parameters are set correctly.  
**Sources**  
[Timestream - AWS IoT Core](https://docs.aws.amazon.com/iot/latest/developerguide/timestream-rule-action.html)  
[AWS IoT Core - Amazon Timestream](https://docs.aws.amazon.com/timestream/latest/developerguide/IOT-Core.html)

Failed to write records to Timestream. The error received was 'Invalid timestamp value ''.

Add your answer

Relevant content