DATEDIF in Days, Hours, and Minutes

Answered

Follow

Mario Valdes II

• October 31, 2019 19:49

Good 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."

1

• 

  Bob Forrester

  • October 31, 2019 21:02
  • Edited

  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

  1

  Comment actions Permalink
• 

  Bob Forrester

  • November 01, 2019 00:17

  Or you can do it all in one column....

  CONCAT( TIMESTAMPDIFF("DAY";#A;#B) ; "d "; MOD(TIMESTAMPDIFF("HOUR";#A;#B);24) ; "h "; MOD(TIMESTAMPDIFF("MINUTE";#A;#B);60) ; "m")

  1

  Comment actions Permalink
• 

  Mario Valdes II

  • November 01, 2019 16:46
  • Edited

  Thanks, Mr. Forrester; your time and effort are much appreciated! Unfortunately, I'm currently on v7.1.12 and the TIMESTAMPDIFF function isn't available; I'll have to work with my boss to see if we're eligible for an upgrade to a more current version.

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • November 01, 2019 16:37

  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:

  1. Convert both of them into a number of milliseconds with the TIMESTAMP function.
  2. Subtract to get the difference between two dates in milliseconds.
  3. 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")

  2

  Comment actions Permalink
• 

  Mario Valdes II

  • November 01, 2019 16:46

  Thank you, Mr. Danyliuk! That worked perfectly!

  0

  Comment actions Permalink

Please sign in to leave a comment.