Need to calculate the difference between two dates with proper date format
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
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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.
0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@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 '
)0 -
HI Guys, not receiving the desired results. :(
0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
This post I've written in the past may be beneficial for your reference on formatting different date differences with the number of days:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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 ')0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive