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!
Solucionado! Ir para Solução.
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:
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
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!!
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:
Hope this helps. We're here for any additional help needed =]
Thanks,
Amit.
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
You are most certainly welcome @Marija Stojkovska? !