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
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 ' )
Time
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.
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.
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
@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 ' )
HI Guys, not receiving the desired results. :(
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.
This post I've written in the past may be beneficial for your reference on formatting different date differences with the number of days:
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 ')
