Calculate elapsed time between two dates

[Rick Lipkin]

To All

Does anyone have a solution to find the elapsed time between two dates in minutes ..

Code: Select all  Expand view

Date out             Time Out           military time
06\18\2017         11:00pm           23:00

Date back            Time Back
06\19\2017         12:30am           12:30
 

The answer is 90 minutes ... I have no problem calculating time on the same date .. but spanning days ( midnight ) is giving me a headache .. any advice or algorithm would be appreciated.

Rick Lipkin

[ukoenig]

Rick,
You can try

Code: Select all  Expand view

/*
 *     Return elapsed time between two days and/or times
 *  SYNTAX
 *     ELAPSED([ <dStart> ], [ <dEnd> ], ;
 *                <cTimeStart>, <cTimeEnd>) -> aTimedata
 *  ARGUMENTS
 *     <dStart> is any valid date in any date format. Defaults to DATE().
 *
 *     <dEnd> is any valid date in any date format. Defaults to DATE().
 *
 *     <cTimeStart> is a valid Time string of the format 'hh:mm:ss' where
 *     hh is hours in 24-hour format.
 *
 *     <cTimeEnd> is a valid Time string of the format 'hh:mm:ss' where
 *     hh is hours in 24-hour format.
 *  RETURNS
 *     A two-dimensional array containing elapsed time data.
 *  DESCRIPTION
 *     ELAPSED() calculates the elapsed time between two Date/Time events.
 *
 *     It returns an array which contains the following data:
 *
 *     aRetVal[1,1]  Integer Days      aRetVal[1,2] Total Days    (nn.nnnn)
 *     aRetVal[2,1]  Integer Hours     aRetVal[2,2] Total Hours   (nn.nnnn)
 *     aRetVal[3,1]  Integer Minutes   aRetVal[3,2] Total Minutes (nn.nnnn)
 *     aRetVal[4,1]  Integer Seconds   aRetVal[4,2] Total Seconds (nn)
 *  EXAMPLES
 *     ELAPSED(CTOD('11/28/90'), CTOD('11/30/90'), '08:00:00', '12:10:30')
 *     will return:
 *
 *     aRetVal[1,1] ->  2 (Days)        aRetVal[1,2] ->    2.1740  Days
 *     aRetVal[2,1] ->  4 (Hours)       aRetVal[2,2] ->   52.1750  Hours
 *     aRetVal[3,1] -> 10 (Minutes)     aRetVal[3,2] -> 3130.5000  Minutes
 *     aRetVal[4,1] -> 30 (Seconds)     aRetVal[4,2] -> 187830     Seconds
 *  END
 */
 
FUNCTION ELAPSED(dStart, dEnd, cTimeStart, cTimeEnd)
  LOCAL nTotalSec, nCtr, nConstant, nTemp, aRetVal[4,2]
 
  IF ! ( VALTYPE(dStart) $ 'DC' )
     dStart := DATE()
  ELSEIF VALTYPE(dStart) == 'C'
     cTimeStart := dStart
     dStart     := DATE()
  ENDIF
 
  IF ! ( VALTYPE(dEnd) $ 'DC' )
     dEnd := DATE()
  ELSEIF VALTYPE(dEnd) == 'C'
     cTimeEnd := dEnd
     dEnd     := DATE()
  ENDIF
 
  IF( VALTYPE(cTimeStart) != 'C', cTimeStart := '00:00:00', )
  IF( VALTYPE(cTimeEnd)   != 'C', cTimeEnd   := '00:00:00', )
 
  nTotalSec  := (dEnd - dStart) * 86400                              + ;
                VAL(cTimeEnd)   *  3600                              + ;
                VAL(SUBSTR(cTimeEnd,AT(':', cTimeEnd)+1,2)) * 60     + ;
                IF(RAT(':', cTimeEnd) == AT(':', cTimeEnd), 0,         ;
                VAL(SUBSTR(cTimeEnd,RAT(':', cTimeEnd)+1)))          - ;
                VAL(cTimeStart) * 3600                               - ;
                VAL(SUBSTR(cTimeStart,AT(':', cTimeStart)+1,2)) * 60 - ;
                IF(RAT(':', cTimeStart) == AT(':', cTimeStart), 0,     ;
                VAL(SUBSTR(cTimeStart,RAT(':', cTimeStart)+1)))
 
  nTemp := nTotalSec
 
  FOR nCtr = 1 to 4
     nConstant := IF(nCtr == 1, 86400, IF(nCtr == 2, 3600, IF( nCtr == 3, 60, 1)))
     aRetVal[nCtr,1] := INT(nTemp/nConstant)
     aRetval[nCtr,2] := nTotalSec / nConstant
     nTemp -= aRetVal[nCtr,1] * nConstant
  NEXT
 
RETURN aRetVal
 

regards
Uwe

[Rick Lipkin]

Uwe

I found your post ... thank you, the only problem I have is when you span a day I do not get the correct calculation .. here is the code I used with your function :

Code: Select all  Expand view

//  dStart = 03/11/2017
//  dEnd   = 03/12/2017    ... notice dENd was incremented below
 
   tStart := strzero( c->Mil_Out,4,0 )           //
   tEnd   := strzero( c->Mil_In, 4,0 )

   tStart := substr(tStart,1,2)+":"+substr(tStart,3,2)+":00"     // 23:00:00
   tEnd   := substr(tEnd,1,2)  +":"+substr(tEnd,3,2)+":00"      // 12:30:00

   If Val( substr( tEnd, 1, 2 ) ) < Val( substr( tStart, 1, 2 ) )
      dEnd++    // 03/13/2017
   Endif
   aDiff := _Elapsed( dStart, dEnd, tStart, tEnd )

   xBrowse( aDiff )

   xTime := aDiff[3,1]  // minutes .. for spanned days may have to add hours

   msginfo( "xTime" )
   msginfo( xTime )

   c->Min := xTime

   msginfo(aDiff[1,1])// -> Days
   msginfo(aDiff[2,1]) // -> Hours
   msginfo(aDiff[3,1]) // -> Minutes
   msginfo(aDiff[4,1]) // -> Seconds

 

date out time out
03/11/2017 11:00pm 2300

Date Back time back
03/12/2017 01:30am 01:30

The result should be 90 minutes .. Tell me how to interpret what I am looking at ?

Thanks
Rick Lipkin

[nageswaragunupudi]

The answer is 90 minutes ... I have no problem calculating time on the same date .. but spanning days ( midnight ) is giving me a headache .. any advice or algorithm would be appreciated.

If you want the answer in minutes:

Code: Select all  Expand view

? ( CTOT( DTOC( dEnd ) + " " + tEnd ) - CTOT( DTOC( dStart ) + " " + tStart ) ) * 24 * 60, "minutes"
 

It is high time that we start using DateTime type and stop using Date and Time separately.
Anyhow, let is calculate the time difference for the above sample

Code: Select all  Expand view

  // Starting Values
   local dStart, dEnd, tStart, tEnd


   SET DATE AMERICAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   // Starting Values
   dStart   := CTOD( "03/11/2017" )
   tStart   := "23:00:00"
   dEnd     := CTOD( "03/12/2017" )
   tEnd     := "12:30:00"

   // Calculation
   ? ElapDTime( dStart, tStart, dEnd, tEnd ) // 13:30:00

return nil

//----------------------------------------------------------------------------//

function ElapDTime( dStart, tStart, dEnd, tEnd )

   local tDiff, cRet  := ""

   tDiff    := CTOT( DTOC( dEnd ) + " " + tEnd ) - CTOT( DTOC( dStart ) + " " + tStart )
   if tDiff >= 1.0
      cRet  := LTrim( STR( Int( tDiff ) ) ) + ' days and '
      tDiff := tDiff % 1
   endif
   cRet +=  SecToTime( tDiff * 24 * 3600 )

return cRet

//----------------------------------------------------------------------------//

 

[Rick Lipkin]

Rao

Thank you ... I saw a similar solution on the internet but did not know how to change the value of the character to valtype "T" .. I agree with you on getting away from Date and Time ... DateTime is one of the foundation blocks in any Sql RDMS.

Thank you Again!
Rick Lipkin

[Rick Lipkin]

Rao

I am having problems ... these are the values I am using :

Code: Select all  Expand view

dEnd   := ctod("03/12/2017" )
dStart := ctod("03/11/2017" )

tStart := "23:00:00"  // 11:00 pm
tEnd   := "12:30:00"  // 12:30 am

nMin := ( CTOT( DTOC( dEnd ) + " " + tEnd ) - CTOT( DTOC( dStart ) + " " + tStart ) ) * 24 * 60

msginfo( "nMin" )
msginfo( nMin )   //  810.0000
 

The correct value is 90 minutes ..

In your example :

Code: Select all  Expand view

// Starting Values
   local dStart, dEnd, tStart, tEnd


   SET DATE AMERICAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   // Starting Values
   dStart   := CTOD( "03/11/2017" )
   tStart   := "23:00:00"
   dEnd     := CTOD( "03/12/2017" )
   tEnd     := "12:30:00"

   // Calculation
   ? ElapDTime( dStart, tStart, dEnd, tEnd ) // 13:30:00

return nil

//----------------------------------------------------------------------------//

function ElapDTime( dStart, tStart, dEnd, tEnd )

   local tDiff, cRet  := ""

   tDiff    := CTOT( DTOC( dEnd ) + " " + tEnd ) - CTOT( DTOC( dStart ) + " " + tStart )
   if tDiff >= 1.0
      cRet  := LTrim( STR( Int( tDiff ) ) ) + ' days and '
      tDiff := tDiff % 1
   endif
   cRet +=  SecToTime( tDiff * 24 * 3600 )

return cRet
 

What does the value 13:30:00 represent ... the correct answer is 90 minutes ?

Rick Lipkin

[nageswaragunupudi]

Correct value is not 90 minutes
From night 11:00 pm to next day 12:30 pm is 13 hours and 30 minutes but not 90 minutes
The formula's answer is correct.


90 minutes after 11 pm is 00:30 on the next day

If you try with tEnd as "00:30:00" then you get 90 minutes.
Please note that "12:30:00" is afternoon not early hours. Early hours are "00:30:00"

[Rick Lipkin]

Rao

The values are from 11:00pm on 03/11/2017 and return is 12:30 am 03/12/2017 .. 90 mins this trip spans midnight from 11:00pm to 12:20 am

Military time 11:00pm = 23:00:00 03/11/2017
Miltary Time 12:30am = 12:30:00 03/12/2017


Rick Lipkin

[Rick Lipkin]

Rao

Let me take a deep breath ... you are correct ... I cannot focus this morning due to all the interruptions .. Let me re-test.

Thanks
Rick Lipkn

[Rick Lipkin]

Rao

Both your examples work great ... I just inserted the wrong value ( as you mentioned ) for 12:30am should have been 0030 .. and NOT 12:30

Rick Lipkin

[nageswaragunupudi]

For computing purposes, please use 24 hour format

[reinaldocrespo]

Hey Rick!

Thank you posting and thank you for giving me something to smile about today. I'm here reading your text and smiling at the fact that you were -for a minute- confused about 12:30am being 00:30.

Many won't understand that in the US you never think of time in "military" terms which is the only way to tell time in Europe. Thus, we have to do a small mental exercise which, at times, can be slippery and it happens to me all the time.

Keep coding, my friend.



Reinaldo.