Cannot convert timestamps to datetime

[jay_pee]

Hello,

I have a column in my data with what looks like timestamps in, but I'm struggling to convert these into meaningful date times in the load editor. The data before the . is always 5 digits long but after the . there's a mixture of different length values - Could this be causing the issue?

Some sample values include...

45208.389583333
45208.41875
45208.640972222
45205.33125
45205.5
45205.448611111

If I add a duplicate column in the chart table with Timestamp(FIELD_NAME) the value converts correctly but if I try to do this in the load editor the value always stays as the timestamp value.

Am I doing something wrong?

[Mark_Little]

Hi,

Have you tried setting the format in script?

Timestamp( A,'YYYY-MM-DD hh.mm')

For example

Mark

[anat]

LOAD *,Date(dt) as date,time(dt) as time,Timestamp(dt) as ts;
load * Inline [
dt
45208.389583333
45208.41875
45208.640972222
45205.33125
45205.5
45205.448611111

];

[jay_pee · Accepted solution]

Thank you everyone for your replies, I've discovered why this particular column wasn't converting correctly.

The table in question is created from two separate resident loads  - The first resident load (Which sets the field order and the column types for the new table I think) had the value set as Null() as this source doesn't have any timestamp values, and the second resident load contains all the timestamp values I listed above. So the column type was probably set as string because of my initial Null() value.

I've now set the Null() field value to be Timestamp(Null()) instead and the conversion is now working as expected.

Took a little while to figure out but got there in the end - Every day's a school day 🙂

[acorneailAN]

Thank you for sharing your solution. I was doing a resident concatenation of three tables and one of them was bringing the date in as a string. I group at the end and was applying date functions that were being ignored and I couldn't figure out why. Now I can stop beating my head against a wall! 

[jay_pee]

Great stuff @acorneailAN , glad you got the issue sorted 😊