Getting DateDiff to show decimal places

Reply
Highlighted
Green Belt

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.

 

Screen Shot 2018-10-11 at 3.05.50 PM.pngScreen Shot 2018-10-11 at 2.24.51 PM.png

 

Any thoughts? Thanks

 


Accepted Solutions
Major Blue Belt

Re: Getting DateDiff to show decimal places

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.
Major Blue Belt

Re: Getting DateDiff to show decimal places

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.

All Replies
Major Blue Belt

Re: Getting DateDiff to show decimal places

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.
Green Belt

Re: Getting DateDiff to show decimal places

Thanks, Brian (@Property_Ninja); if doing this in MySQL, your suggestion would be done in a transform, correct? 

Major Blue Belt

Re: Getting DateDiff to show decimal places

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.
Green Belt

Re: Getting DateDiff to show decimal places

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

Major Blue Belt

Re: Getting DateDiff to show decimal places

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.
Announcements
Dojo Day is Coming! Mark your calendars for May 30th 0900-1700 MST Click here for more information