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
-
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
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 467 Domo Developer
- 50 Domo Everywhere
- 106 Apps
- 717 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 27 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 654 ひらめき共有