KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Time difference between two "timestamps"
PRODUCT: 4D | VERSION: | PLATFORM: Mac & Win
Published On: February 14, 2002
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

Version: 6.5.x and 6.7.x

Many database management systems have a "timestamp" data type. 4th Dimension does not, although it is a very useful thing. A "timestamp" is a combination of the Date + Time. It can easily be expressed as a 19-character string. For instance, if the date and time is February 9, 2002 at 9:12 AM, the timestamp value would be: 2002-02-09 09:12:00.

If you work with timestamps, it is sometimes useful to know the difference between two timestamp values. Here is a simple method that finds the Number of hours that separate two timestamp values.


`Method: GEN_TimestampDifference

`Example: $hHours:=GEN_TimestampDifference($timestamp1;$timestamp2)

C_STRING(19;$1;$2)

C_TIME($0)

C_STRING(19;$sTimestamp1;$sTimestamp2)


$0:=†00:00:00†


Case of


 : (Count parameters<2)

 : (Length($1)#19)

 : (Length($2)#19)

 : ($1=$2)

  Else


  If ($1>$2)  `if 1st timestamp is the most recent

   $sTimestamp1:=$1

   $sTimestamp2:=$2

 Else

    $sTimestamp1:=$2

   $sTimestamp2:=$1

   End if
  

C_LONGINT($iYear;$iMonth;$iDay)

  C_DATE($dDate1;$dDate2)
  

$iYear:=Num(Substring($sTimestamp1;1;4))

  $iMonth:=Num(Substring($sTimestamp1;6;2))

  $iDay:=Num(Substring($sTimestamp1;9;2))-1

  $dDate1:=Add to date(!00/00/00!;$iYear;$iMonth;$iDay)
  
$iYear:=Num(Substring($sTimestamp2;1;4))

  $iMonth:=Num(Substring($sTimestamp2;6;2))

  $iDay:=Num(Substring($sTimestamp2;9;2))-1

  $dDate2:=Add to date(!00/00/00!;$iYear;$iMonth;$iDay)


  C_TIME($hHours;$hTime1;$hTime2)


  $hHours:=(24*†01:00:00†)*($dDate1-$dDate2)

  $hTime1:=Time(Substring($sTimestamp1;12))

  $hTime2:=Time(Substring($sTimestamp2;12))

  $0:=$hHours+($hTime1-$hTime2)


End case