Need to calculate the difference between two dates with proper date format

Dheeraj_1996
Dheeraj_1996 Member
edited July 2023 in Beast Mode

Need to calculate the difference between two dates in Days Hours Mins seconds format.

Wrote the below case for date difference but not getting the required output.

Output.png
Beastmode1.png

Getting output as shown above in yellow:

Need output as : 4days 21 Hrs 20Min 27sec

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    You can use the SPLIT_PART function and CONCAT function to build this. It would look something like this:

    CONCAT(
    
split_part(Time,' ',1),' days ',
    
RIGHT(split_part(Time,':',1),2),' hours ',
    
split_part(Time,':',2),' minutes ',
    
split_part(split_part(Time,':',3),'.',1),' seconds '
    
)

    You would need to add additional case logic if you didn't want the days to show if there were no days.

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Dheeraj_1996
    Dheeraj_1996 Member

    Hi Mark, thanks this worked for one of the rows. But for other row it is not working where the day is 0. can you please help.

    MicrosoftTeams-image (5).png

  • MarkSnodgrass
    MarkSnodgrass Coach

    It looks like your data has extra spaces at the end, which is why it is displaying the full value at the beginning when there is no day at the start. Adding a TRIM function around the field would help. Also, looking to see if there is a space towards the start of the string to see if there is a day value will work as well. You could do this:

    CASE WHEN LEFT(INSTR(Time,' '),6) > 1 THEN
    
CONCAT(
    
split_part(Time,' ',1),' days ',
    
RIGHT(split_part(Time,':',1),2),' hours ',
    
split_part(Time,':',2),' minutes ',
    
split_part(split_part(Time,':',3),'.',1),' seconds '
    
)
    
ELSE
    
CONCAT(
    
RIGHT(split_part(Time,':',1),2),' hours ',
    
split_part(Time,':',2),' minutes ',
    
split_part(split_part(Time,':',3),'.',1),' seconds '
    
)
    
END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • marcel_luthi
    marcel_luthi Coach
    edited July 2023

    @MarkSnodgrass awesome approach, definitely cleaner than what I was thinking about. Tested and works perfectly except for when the outcome does not includes a day part. Adding two case statements to it can help solve for those instances.

    image.png 
    CONCAT(
    
CASE WHEN `dt`  < CURRENT_TIMESTAMP() THEN '-' ELSE '' END,
    
CASE WHEN split_part(`dt` - CURRENT_TIMESTAMP(),' ',1) * 1 = split_part(`dt` - CURRENT_TIMESTAMP(),' ',1) THEN CONCAT(ABS(split_part(dt - CURRENT_TIMESTAMP(),' ',1)), ' days ') ELSE '' END,
    
RIGHT(split_part(`dt` - CURRENT_TIMESTAMP(),':',1),2),' hours ',
    
split_part(`dt`  - CURRENT_TIMESTAMP(),':',2),' minutes ',
    
split_part(split_part(`dt` - CURRENT_TIMESTAMP(),':',3),'.',1),' seconds '
    
)

  • Dheeraj_1996
    Dheeraj_1996 Member
    MicrosoftTeams-image (6).png

    HI Guys, not receiving the desired results. :(

  • MarkSnodgrass
    MarkSnodgrass Coach

    When troubleshooting these kinds of things, I will temporarily create multiple beast modes that is just extracting a single component so I can see where the logic is off. Try making a beast mode just for days using the logic for days and see if that reveals to you what additional logic you might need to add.

    My days logic is looking for what is just before the first space and your result doesn't seem to be corresponding to that. Breaking it down into smaller pieces can help reveal the issue.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    This post I've written in the past may be beneficial for your reference on formatting different date differences with the number of days:

    https://community-forums.domo.com/main/discussion/52682/domo-ideas-conference-beast-modes-time-difference-formatting

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

    HI,

    I am using the below format for the date transformation. but not giving the proper format.

    CONCAT(CASE WHEN split_part(`auditdate`- `submitdate`,' ',1) * 1 = split_part(`auditdate`- `submitdate`,' ',1) THEN CONCAT(ABS(split_part(`auditdate`- `submitdate`,' ',1)), ' days ') ELSE '' END,
    RIGHT(split_part(`auditdate`- `submitdate`,':',1),2),' hours ',split_part(`auditdate`- `submitdate`,':',2),' minutes ',split_part(split_part(`auditdate`- `submitdate`,':',3),'.',1),' seconds ')

    MicrosoftTeams-image (8).png

Categories