Getting DateDiff to show decimal places
We're using two date fields in a card and are trying to show a difference between the two. The source data shows these w/decimal places (screenshot) but our card didn't, couldn't understand why but when viewing the data table at the bottom of the card, it seemed it might be caused by the PickupDate being YYYY-MMM only, whereas the other date field is YYYY-MM-DD hh:mm:ss.
With that in mind, figured it should be a simple fix and went on to update the existing beast mode from ABS(DATEDIFF(IFNULL(`DocumentationReviewDate`,CURRENT_DATE()),`PickupDate`)) to ABS(DATEDIFF(IFNULL(`DocumentationReviewDate`,CURRENT_DATE()),TIMESTAMP(`PickupDate`))) but we're still getting a whole number returned for the date difference, which is keeping us from being able to reconcile the card with the source data.
Any thoughts? Thanks
Best Answers
-
Hi John,
I am not sure you can actually get this result from a BeastMode. You will most likely have to do the transformation within a MySQL flow with something like ...
timestampdiff(second, '2018-10-11 13:01:01', '2018-10-08 09:01:01') / (24 * 60 * 60) as day_dec_diff
Hope this helps,
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 -
Hi John,
You can actually do this one in beastmode.
CONCAT(DATEDIFF('2018-10-31 14:31:26','2018-10-11 07:01:01'),' days, ',ROUND(TIME_TO_SEC(TIMEDIFF('2018-10-31 14:31:26','2018-10-11 07:01:01'))/3600,1),' hours')
This formula will return the format of '20 days, 7.5 hours'
Hope this helps,
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
Answers
-
Hi John,
I am not sure you can actually get this result from a BeastMode. You will most likely have to do the transformation within a MySQL flow with something like ...
timestampdiff(second, '2018-10-11 13:01:01', '2018-10-08 09:01:01') / (24 * 60 * 60) as day_dec_diff
Hope this helps,
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 -
Thanks, Brian (@Property_Ninja); if doing this in MySQL, your suggestion would be done in a transform, correct?
0 -
Yes, that is correct
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0 -
Brian (@Property_Ninja),
Sorry for not responding sooner on this! Just wondering, if we wanted to show the number of days plus remaning hours (less than a 24 hours period, do you know how we'd show that in a MySQL TimestampDiff transform? Thanks!
EXAMPLE:
TimeStamp 1: 2018-10-11 07:01:01
TimeStamp 2: 2018-10-31 14:31:26'
Is there a way to show that difference as 20 days, 7.5 hours (or something along those lines)
John
0 -
Hi John,
You can actually do this one in beastmode.
CONCAT(DATEDIFF('2018-10-31 14:31:26','2018-10-11 07:01:01'),' days, ',ROUND(TIME_TO_SEC(TIMEDIFF('2018-10-31 14:31:26','2018-10-11 07:01:01'))/3600,1),' hours')
This formula will return the format of '20 days, 7.5 hours'
Hope this helps,
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
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive