Transforms Creating Nulls for timestamp

Question

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 previewing the code above, you can see some null values. Also, if you “run” the code it complains about nulls. (Not pictured.)

I think I’ve confirmed that the DateTimeStamp column in `ISTC_WW_InSQL`.`wwHistory` contains no null values.

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?

Chris Selph · Accepted Answer

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 theto_timestamp(DateTimeStamp, 'yyyy-M-d HH:mm:ss') Is Not Nullfor good measure.

Gaetan Helness · Answer

Hi Chris,I guess the “to_timestamp” is not able to convert theDateTimeStamp on the specified formatIf you comment the last line again and printDateTimeStamp, something like thatdp.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 ofDateTimeStamp 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 theDateTimeStamp look like and we can see

Sign up

Welcome to Cognite Hub