topic Cannot convert timestamps to datetime in App Development

Cannot convert timestamps to datetime

jay_pee — Tue, 10 Oct 2023 10:48:11 GMT

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?

Re: Cannot convert timestamps to datetime

Mark_Little — Tue, 10 Oct 2023 11:04:33 GMT

Hi,

Have you tried setting the format in script?

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

For example

Mark

Re: Cannot convert timestamps to datetime

anat — Tue, 10 Oct 2023 13:40:53 GMT

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

];

Re: Cannot convert timestamps to datetime

jay_pee — Wed, 11 Oct 2023 08:13:40 GMT

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 🙂

Re: Cannot convert timestamps to datetime

acorneailAN — Mon, 07 Apr 2025 15:01:11 GMT

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!

Re: Cannot convert timestamps to datetime

jay_pee — Tue, 22 Apr 2025 09:17:12 GMT

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