Simple DATEDIFF Question

user32470
user32470 Member
in Magic ETL

As I understand it DATEDIFF will return (in number of days) the difference between two sets of dates. My dataset is pretty rudimentary so it seemed pretty cut and dry but when I do DATEDIFF (for days) it returns an incorrect figure.

 

This is the beast mode I am using:

 

DATEDIFF(`Form Complete Date`, `Form Sent Date`)

 

I attached a screenshot showing the Form Sent Date (10/14/2020) and Form Complete Date (11/13/2020). Not sure why it's returning 5 Days...the aggregation is COUNT since we're counting up the number of days between sent/completion. I tried sum just to see and its returning 150 days...

 

thanks!!

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user32470 

     

    I've seen the DATEDIFF function not always work as intended some times so I utilize the UNIX_TIMESTAMP function to convert it to the number of seconds since 1970-01-01 and then do some simple math.

     

    ROUND(((UNIX_TIMESTAMP(`Form Complete Date`) - UNIX_TIMESTAMP(`Form Sent Date`)) / 60 / 60 / 24) - 0.5, 0)

     

    Subtracting 0.5 and using ROUND is the same as truncating a number which gives you the number of whole days in-between the two dates.  60/60/24 converts the difference in seconds to the difference in days.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • user32470
    user32470 Member

    The pivot card is set to "ALL TIME" ...It's just showing 10/12/20 because the date range field defaulted to a 3rd date column. It shouldn't affect the beast mode, but I changed the date range field just to double check and its still returning 5 days ...

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user32470 

     

    I've seen the DATEDIFF function not always work as intended some times so I utilize the UNIX_TIMESTAMP function to convert it to the number of seconds since 1970-01-01 and then do some simple math.

     

    ROUND(((UNIX_TIMESTAMP(`Form Complete Date`) - UNIX_TIMESTAMP(`Form Sent Date`)) / 60 / 60 / 24) - 0.5, 0)

     

    Subtracting 0.5 and using ROUND is the same as truncating a number which gives you the number of whole days in-between the two dates.  60/60/24 converts the difference in seconds to the difference in days.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user32470
    user32470 Member

    Thanks, Grant! You've been more than helpful with me the past 2 days!! I like the inclusion of the Unix Timestamp - learned something new.

     

    I found out why it wasn't returning the correct number of days from my first attempt - I was using a pivot table and wasn't aware the function returns the number of days "as-is" without needing aggregation. I switched it over to the "megatable" which allows no aggregation and voila we have the right count.

     

    thanks again!

  • user080280
    user080280 Member

    It did not work for me:

    ROUND(((UNIX_TIMESTAMP(`UnderwritingClearToCloseDate`) - UNIX_TIMESTAMP(`UnderwritingSubmittedDate')) / 60 / 60 / 24) - 0.5, 0)  I can clearly see the 2 fields with dates

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    ... what didn't work?  where did it break?

    i guarantee you the ROUND() function works and UNIX_TIMESTAMP() works.

     

    so why doesn't it work with your beast mode?  peel away the layers.  remove ROUND() function.  does it work?  Remove the math.  you probably have a parenthesis in the wrong place.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories