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

Dheeraj_1996

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.

Getting output as shown above in yellow:

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

MarkSnodgrass

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.

Dheeraj_1996

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.

MarkSnodgrass

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

marcel_luthi

@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.

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

HI Guys, not receiving the desired results. :(

MarkSnodgrass

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.

GrantSmith

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

Dheeraj_1996

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