Skip to main content
Solved

Transforms Creating Nulls for timestamp

Chris Selph
Seasoned
Forum|alt.badge.img

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?

 

Best answer by Chris Selph

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.

View original
    Did this topic help you find an answer to your question?

    2 replies

    G
    MVP
    Forum|alt.badge.img

    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

    Chris Selph
    Seasoned
    Forum|alt.badge.img
    • Chris Selph
    • Author
    • Seasoned
    • 26 replies
    • Answer
    • March 17, 2023

    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.

    Reply


    Terms & ConditionsCookie Settings

    Cookie Policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie Settings