+4Hello,
I hoped somebody has already worked this out so I don’t have to 🤣.
I have a Epoch Unix Timestamp value that I want to convert into normal DATETIME format in Ataccama.
e.g. 1711015200
I was expecting converted value to be 2024-03-21 10:00:00 (GMT).
But when I do a toDateTime() on it, it gives me 1970-01-20 20:16:55. So I guessed wrong.
I thought about using dateadd() but that one does not support seconds.
Any ideas?
Best answer by may_kwok
A kind soul has shared with me a formula that does exactly what I need:
dateAdd(now(), -1 * toInteger(toString(now(), "Z")) div 100, "HOUR")
(Of course, Replace “now()” with whatever datetime value you have)
toDateTime expect number of milliseconds, while your value is in seconds. To get expected value you need to multiply it by 1000.
+4Hi
I’m now hit by daylight savings…!
I need the toDatetime to give me UTC time, but at the moment in the UK it’s giving me British Summer Time.
I see if a datetime is string I can specify locale. But I can’t find what locale, or syntax that is required to achieve this.
Any ideas?
Thanks!
+4My exact problem:
I call an API, it gives me the unix timestamp back, say for example: 1714035600.
This translates to 2024-04-25 09:00:00 UTC, or 2024-04-25 10:00:00 BST.
Because my ONE Desktop observes daylight savings, the toDateTime() expression gives me 2024-04-25 10:00:00.
I store this down into RDM. RDM thinks this is UTC.
So I now have RDM Web UI displaying this as 2024-04-25 11:00:00.
😭
+4A kind soul has shared with me a formula that does exactly what I need:
dateAdd(now(), -1 * toInteger(toString(now(), "Z")) div 100, "HOUR")
(Of course, Replace “now()” with whatever datetime value you have)
+4We also were experiencing the same issue. RDM imports in UTC, but renders in local time. Adding a fixed number will not work, because it does not take into acount the daylight savings. Your formula seems to be the fix indeed, thanks a lot! I also added a ticket to support as this seems to be a problem more generic in the product, and should be added either as an option somewhere or at least show in the documentation these issues.
+4Hi
My wish list is that there is an RDM domain format option to always display in UTC. I have submitted feedback in link so hopefully it’ll be considered. (Please upvote!)
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
