Solved

Transforms Creating Nulls for timestamp

Chris Selph
Rank
Userlevel 2
Badge

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?

 

icon

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

View original

2 replies

Chris Selph
Rank
Userlevel 2
Badge

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.

G
Rank
Userlevel 3
Badge

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

Reply


Terms & ConditionsCookie Settings