Getting DateDiff to show decimal places

John-Peddle
John-Peddle Contributor
in Magic ETL

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

 

Best Answers

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    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.
  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    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.

Answers

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    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.
  • John-Peddle
    John-Peddle Contributor

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

  • Property_Ninja
    Property_Ninja Contributor

    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.
  • John-Peddle
    John-Peddle Contributor

    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
    Property_Ninja Contributor
    Answer ✓

    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.

Categories