How to get string timestamp from IoT Core into SitWise

Question

I'm trying to get some data into SiteWise through IoT Core and I keep getting this result from the rule:

> "details": "No requests were sent. PutAssetPropertyValueEntries was empty after performing substitution templates. Message arrived on: , Action: iotSiteWise"

I think the reason is because of this:

> Note
Because an expression in a substitution template is evaluated separately from the SELECT statement, you can't use a substitution template to reference an alias created using an AS clause. You can reference only information present in the original payload, in addition to supported functions and operators.

I am ATTEMPTING to do this as the source:

```
SELECT fields.rms0, fields.rms1, fields.rms2, time_to_epoch(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")/1000 as ts
```

but I see from the document that won't work.  In the rule, I tried it another way: in the timeStamp mapping (in the UI) I tried to set the time to:

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

but that doesn't work, either - it gave me an error saying it didn't understand time_to_epoch.

Next, I tried going back to this:

```
SELECT *
```

and in the Rule to write to SiteWise I set all the timestamp fields to:

```
${floor(time_to_epoch(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") / 1E3)}
```

following the manual.

```
    "resources": {
        "FunctionName": "time_to_epoch"
    },
    "details": "Undefined result"
```
```
    "resources": {
        "FunctionName": "floor"
    },
    "details": "Undefined result"
```

so I am not having much success. Is there any way to make it log what the BatchPutAssetPropertyValue request contains?

Extra thought: is this whole problem because my timestamp column is named 'timestamp' ?

Accepted Answer

I simulated by publishing this message to IoT:

```
{
  "rms0": 1.4,
  "rms1": 2.2,
  "timestamp": "2022-03-10T08:26:00.500Z"
}
```

With the Rule having`SELECT *` and the following for **Time in seconds**:

```
${floor(time_to_epoch(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") / 1E3)}
```

And the data landed in Sitewise with a timestamp of `March 10, 2022, 16:26:00 (UTC+8:00)`.

I hope this helps.

How to get string timestamp from IoT Core into SitWise

Relevanter Inhalt