Calculate elapsed time between two dates

Calculate elapsed time between two dates

Postby Rick Lipkin » Mon Jun 19, 2017 6:39 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby ukoenig » Mon Jun 19, 2017 6:57 pm

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 :D
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
User avatar
ukoenig
 
Posts: 4043
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany

Re: Calculate elapsed time between two dates

Postby Rick Lipkin » Mon Jun 19, 2017 9:16 pm

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

 


Image

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby nageswaragunupudi » Mon Jun 19, 2017 10:27 pm

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

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

 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Calculate elapsed time between two dates

Postby Rick Lipkin » Tue Jun 20, 2017 1:02 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby Rick Lipkin » Tue Jun 20, 2017 1:52 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby nageswaragunupudi » Tue Jun 20, 2017 2:05 pm

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"
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Calculate elapsed time between two dates

Postby Rick Lipkin » Tue Jun 20, 2017 2:07 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby Rick Lipkin » Tue Jun 20, 2017 2:12 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby Rick Lipkin » Tue Jun 20, 2017 2:17 pm

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
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Calculate elapsed time between two dates

Postby nageswaragunupudi » Tue Jun 20, 2017 2:20 pm

For computing purposes, please use 24 hour format
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10207
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Calculate elapsed time between two dates

Postby reinaldocrespo » Sat Jun 24, 2017 4:18 pm

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.
User avatar
reinaldocrespo
 
Posts: 971
Joined: Thu Nov 17, 2005 5:49 pm
Location: Fort Lauderdale, FL


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Willi Quintana and 8 guests