Using FROM_UNIXTIME Correctly

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?

Best Answer

  • 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


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • 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


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • 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.

     

    DomoProblem2.PNG

  • Can you try it without formatting, so just

     

    from_unixtime(yourDate)

     

    I just tested and this worked for me.

     

    Thanks,

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • If this doesn't work, you should make sure your field is actually in UNIX time


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • 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