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.
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Trifacta Classic
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- multiply the column by 1000
- concatinate with 000
- Enter suffix of 3 zeroes ("000")
Hope this helps. We're here for any additional help needed =]
Thanks,
Amit.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You are most certainly welcome @Marija Stojkovska? !
