Getting DateDiff to show decimal places

John-Peddle

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

 

Property_Ninja

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

Property_Ninja

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

Property_Ninja

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

John-Peddle

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

Property_Ninja

Yes, that is correct

John-Peddle

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

Property_Ninja

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