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.3
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.1 -
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.
0 -
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.0 -
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.0 -
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-apis0 -
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.3
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive