DATEDIF in Days, Hours, and Minutes
AnsweredGood afternoon, all,
Is it possible to calculate the duration between two dates and have the results return in a "Days, Hours and Minutes" format? E.g.: the result of the difference between "10/21/19 16:14" and "10/23/19 18:54" would be "2d 2h 40m."

Really easy , all you need is TimestampDiff and Mod.
First we have 2 times in different columns:
Next we will use TIMESTAMPDIFF to find days apart:
Then we will use MOD with TIMESTAMPDIFF to get the hours:
Since we have already done days, this time we will take mod of 24 hours.
Do the same thing with Minutes:
And finally use Concat to put the values into any format you would like:
https://www.datameer.com/documentation/display/DAS70/TIMESTAMPDIFF
https://www.datameer.com/documentation/display/DAS70/MOD
https://documentation.datameer.com/documentation/display/DAS70/CONCAT

Hello Mario.
I guess it is also possible to perform a similar calculation without TIMESTAMPDIFF, using TIMESTAMP and MOD functions.To determine number of days between two date values:
 Convert both of them into a number of milliseconds with the TIMESTAMP function.
 Subtract to get the difference between two dates in milliseconds.
 Divide the result to 86400000 (# of ms in one day) and round down.
Column Days: INT(ROUNDDOWN((TIMESTAMP(#B)TIMESTAMP(#A))/86400000))
Use similar approach to get hours, minutes and seconds (if needed).
Column Hours: INT(ROUNDDOWN((MOD(TIMESTAMP(#B)TIMESTAMP(#A);86400000))/3600000))
Column Minutes: INT(ROUNDDOWN(MOD(TIMESTAMP(#B)TIMESTAMP(#A);3600000)/60000))
Column Seconds: INT(ROUND(MOD(TIMESTAMP(#B)TIMESTAMP(#A);60000)/1000))
Finally, concatenate these four columns to get the complete result.
CONCAT(#Days;"d ";#Hours;"h ";#Minutes;"m ";#Seconds;"s")
Please sign in to leave a comment.
Comments
5 comments