Solved

Convert unix timestamp to datetime

  • 21 March 2024
  • 9 replies
  • 88 views

Userlevel 3
Badge +3

Hello,

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?

icon

Best answer by maykwok_hamilton 25 April 2024, 16:15

View original

9 replies

Userlevel 5
Badge +3

Hi @maykwok_hamilton 

toDateTime expect number of milliseconds, while your value is in seconds. To get expected value you need to multiply it by 1000.

Userlevel 3
Badge +3

Awesome, that worked, thank you!!

Userlevel 3
Badge +3

Hi @AKislyakov ,

 

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!

Userlevel 3
Badge +3

My 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.

😭

Userlevel 3
Badge +3

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)

Userlevel 6
Badge +4

Thank you for sharing it here @maykwok_hamilton 🙌

Userlevel 1
Badge +3

Hi @maykwok_hamilton 

We 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.

 

Userlevel 3
Badge +3

Hi @Joeri 

 

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!)

 

Userlevel 1
Badge +3

Gladly upvoted it!

Reply