Using FROM_UNIXTIME Correctly

user08583
user08583 Member
in Magic ETL

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

  • Property_Ninja
    Property_Ninja Contributor
    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

  • Property_Ninja
    Property_Ninja Contributor

    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.
  • user08583
    user08583 Member

    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

  • Property_Ninja
    Property_Ninja Contributor

    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.
  • Property_Ninja
    Property_Ninja Contributor

    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.
  • user08583
    user08583 Member

    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
    Property_Ninja Contributor
    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.

Categories