Solved

# Transforms Creating Nulls for timestamp

• 2 replies
• 31 views

Userlevel 2
• Seasoned
• 12 replies

Hi, I’m running a transform from Raw to insert/update Numeric values.

/* MAPPING_MODE_ENABLED: false *//* {"version":1,"sourceType":"raw","mappings":[{"from":"","to":"id"},{"from":"","to":"externalId"},{"from":"","to":"timestamp"},{"from":"","to":"value"}]} */SELECT dp.Tagname AS externalId,to_timestamp(DateTimeStamp, 'yyyy-M-d h:mm:ss') AS timestamp,   cast(value AS double) FROM ISTC_WW_InSQL.wwHistory AS dp  , --selecting from _cdf.timeseries means we select from the timeseries we ingested to CDF earlier. We do this to make sure all the time series we try to add data points to actually exist_cdf.timeseries AS ts WHERE dp.Tagname = ts.externalId--And to_timestamp(DateTimeStamp, 'yyyy-M-d h:mm:ss') Is Not Null

When, we UnComment the last line of code

And to_timestamp(DateTimeStamp, 'yyyy-M-d h:mm:ss') Is Not Null

The preview and the actual Run executes with no errors.

Is this an issue with the to_timestamp function or something else?

icon

Best answer by Chris Selph 17 March 2023, 14:05

View original

### 2 replies

Userlevel 3

Hi Chris,

I guess the “to_timestamp” is not able to convert the DateTimeStamp on the specified format

If you comment the last line again and print DateTimeStamp, something like that

dp.Tagname AS externalId,to_timestamp(DateTimeStamp, 'yyyy-M-d h:mm:ss') AS timestamp,DateTimeStamp as DateTimeStamp,   cast(value AS double) 

And see what the format of DateTimeStamp look like, I would assume it is not 'yyyy-M-d h:mm:ss' ?
You can also test that date you get directly with the timestamp function and try to find the right format.
Potentially have to use something like what we suggested here?

Let me know how the DateTimeStamp look like and we can see

Userlevel 2

this was solved with this:

(to_timestamp(DateTimeStamp, 'yyyy-M-d HH:mm:ss') changing to use HH for 24 hour time.

We also used the to_timestamp(DateTimeStamp, 'yyyy-M-d HH:mm:ss') Is Not Null

for good measure.