Hello - I'm trying to convert unix timestamp to date time format using the unixtimeformat formula. It converts the values to the desired format however it gives the wrong date.

[46238cd6fba4c436815e]

For example, the unix timestamp of 1593577919 corresponds to Mon, 28 Sep 2020 13:00:45 GMT and Dataprep outputs 1970-01-19·10:39:37.919 which is completely off. Below is a screenshot of the formula I'm using, the original column and the results I'm getting. Any help is greatly appreciated, am I doing something wrong or missing out something?

Thank you!

[Trifacta_Alumni]

Hi @Marija Stojkovska?,

Well, I am easily able to reproduce this finding, and am investigating further.

In the meantime, I think it is worth noting that 1593577919 does not correspond to Mon, 28 Sep 2020 13:00:45 GMT in any of the three online conversion tools I checked:

• https://time.is/Unix_time_converter
• https://www.unixtimestamp.com
• https://www.epochconverter.com/

Rather, each of these tools gives the following, instead: Wed, o1 Jul 2020 04:31:59 GMT.

Can you please let me know how you arrived at the September date-time?

Thanks,

Nathanael

[46238cd6fba4c436815e]

Hi @Nathanael Kuipers? , thanks a lot for replying so fast. You are right, it's my mistake. I actually spotted it just before you replied but didn't manage to correct it fast enough, I should have double checked. I copied the wrong date/time from the online conversion tool, today's date instead of Wed, o1 Jul 2020 04:31:59 GMT, they were one above the other, silly mistake - my apologies.

Thank you for further looking into this!!

[AMiller_Tri · Accepted solution]

Hi @Marija Stojkovska? 

The reason for this is because your unix timestamp is the duration in seconds since 1970-01-01 00:00:00, instead of duration in miliseconds.

So, by showing this:

We can see that every "0" that's added to the end of your unix timestamp gets us closer and closer to the desired output.

Only by adding 3 zeros as a suffix to your unix timestamp will you be able to create the correct date.

This is due to the fact that Trifacta's unixtimeformat() function: (documentation)

https://docs.trifacta.com/display/dp/UNIXTIMEFORMAT+Function

measures the number of milliseconds that have elapsed since January 1, 1970 00:00:00 (UTC).

In order to fix the issue, either:

1. multiply the column by 1000
2. concatinate with 000
3. Enter suffix of 3 zeroes ("000")

Hope this helps. We're here for any additional help needed =]

Thanks,

Amit.

[46238cd6fba4c436815e]

Hi @Amit Miller? 

Thank you for the reply! That does make sense, I have tried it and it works perfectly - I multiplied the column by 1000 and used that value in the unixtimeformat() function.

Appreciate all the help!

Marija

[AMiller_Tri]

You are most certainly welcome @Marija Stojkovska? !