Using FROM_UNIXTIME Correctly

user08583

Hello,

 

I think this problem is an easy fix, but it has proven difficult for me. I've imported some data from Hubspot about my contacts, and I'm trying to convert one column from UNIX into a human readable time using a calculated field. (I validated it before and Domo said it was good to go).

 

This is my formula: DATE_FORMAT(FROM_UNIXTIME(`Became a Lead Date`), '%m,%d,%y')

 

What I get is shown in the attached image.

 

Anyone know what I did wrong?

Property_Ninja

ahhhhhh okay, try this to convert it in Beastmode. If it doesn't work in Beastmode you will have to cast it as a BIGINT in a dataflow transform.

from_unixtime(yourDate/1000)

 

-Brian

Property_Ninja

Hi,

 

Instead of using the wrapper of DATE_FORMAT(), could you just use FROM_UNIXTIME(`yourDate`,'%Y-%m-%d')?

 

Let me know if this helps.

 

Thanks,

 

Brian

user08583

Thanks for the suggestion, I did that and now it says the date is invalid. I guess it's a step forward because it's recognizing it as a date.

 

Property_Ninja

Can you try it without formatting, so just

 

from_unixtime(yourDate)

 

I just tested and this worked for me.

 

Thanks,

 

Brian

Property_Ninja

If this doesn't work, you should make sure your field is actually in UNIX time

user08583

It did not work and it is formatted in UNIX time, but in Milliseconds, at least according to Hubspot's API documentation. Are UNIX times not normally in milliseconds?

https://developers.hubspot.com/docs/faq/how-should-timestamps-be-formatted-for-hubspots-apis

Property_Ninja

ahhhhhh okay, try this to convert it in Beastmode. If it doesn't work in Beastmode you will have to cast it as a BIGINT in a dataflow transform.

from_unixtime(yourDate/1000)

 

-Brian