conversion op paradox timestamp field naar double precision floating

[jwt]

I'm looking for a way to convert a Paradox database timestamp field of 8 bytes as double precision floating to convert this to an iso date like yyyddm hh:mm:ss.
According to some found internet information, it is stored as Float value: Integer part as days since 0001-01-01 and fractional part as fraction of a day. It uses the IEEE notations 1 bit sign, 11 bits for the exponent and 52 bits for the matissa.
I try to convert the hex values of the 8 bytes, but have not found a way to do it properly in Winbatch.

Can anybody help me out with this?

Best regards Jan Willem Teunisse

[td]

The script is very rough and makes several assumptions but it will, hopefully, get you started in the correct direction.

Code Select Expand

yearone = "0001:01:01:00:00:00"
millisecondinday = 86400000   ; Milliseconds in day
fdate = 738760.43200  ; A float date.

days = int(floor(fdate))
seconds = int((fdate - days) * millisecondinday)
tdate = TimeAdd(yearone, '0000:00:':days:':00:00:00')
tdate = TimeAdd(tdate, '0000:00:00:00:00:':seconds)
hrdate = TimeFormat(tdate, 'M/dd/yyyy hh:mm:ss')

Message("Float Date ":fdate, "Human readable date: ":hrdate)


[jwt]

Thanks, TD, that's what I figured already out for the second part.

What I'm looking for is the first part is to convert from the hexidecimal representation of the 8 double precision bytes (0xC2CCBE630B8D9C00) to the float date.

[spl]

Off-Topic, but played around with converting the hex for use with the CLR FromFileTime() function. Funny that all conversions I tried resulted in base Gregorian Dec 31, 1600 + hours/min/sec. I also realized my hex2dec conversion routine was flawed and I adjusted in on another thread which was a good thing.

[td]

Thanks, TD, that's what I figured already out for the second part.

What I'm looking for is the first part is to convert from the hexidecimal representation of the 8 double precision bytes (0xC2CCBE630B8D9C00) to the float date.

Sorry that I missed your point. It happens... Your hex representation is a negative floating point number. If you treat the "C2" as the most significant byte, it is represented by 11000010 and has its most significant bit set. The problem with IEEE 754 double precision numbers is that the standard does not specify the endianness of the representation.  It depends partly on the system architecture of the data's source. Also, there are mixed endian representations.

The following script reverses the byte order and produces the negative number. It is the same number produced by online hex to double converters.

Code Select Expand

HexFloat = StrSub("0xC2CCBE630B8D9C00",3,-1)
nSize = Strlen(HexFloat) /2
hBin = BinaryAlloc(nSize)
BinaryPokeHex(hBin, 0, HexFloat)
for i = 0 to (nSize/2)-1
   byte = BinaryPeek(hBin, i)
   temp = BInaryPeek(hBin, (nSize-i) -1)
   BinaryPoke(hBin, (nSize-i) -1, byte)
   BinaryPoke(hBin, i, temp)
next

FloatDateRev = BinaryPeekFlt(hBin, 0)
Message('Reversed Byte Hex to Double', 'Hex: Hex:':HexFloat:' Float: ':FloatDateRev)
exit
If you do not reverse the byte order you get the number 0.00000.

[td]

I should have made this a little clearer. Online hex to double converters produce the same result as the reversed byte order script without needing to  reverse the byte order first. That means the converter assumes big endian whereas Intel/AMD processors are little endian.

[jwt]

Thanks again, TD! In paradox databases the designers used indeed Little Endian for the internal structure and in the datarecords with integer variables it is (mostly) Big Endian, but not every time like in this timestamp variable.
A couple of days ago I tried the following script and the BinaryPeekFlt gives indeed a 0.0000

; C2CCBE630B8D9C00   andere C2CD043D424C3A00
; 1 bit is sign, ; 11 bits is exponent, 52 btes is mantissa
floatdec = 1.000001
flBuf = BinaryAlloc(8)
BinarypokeHex(flBuf, 0, "C2CCBE630B8D9C00")
floatdec = BinaryPeekFlt(flBuf,0)    ; gives  0.00000
hugenr =  BinaryPeek8(flBuf,0)       ; returns 44065176424467650
byte = BinaryPeek(flBuf,0)           ; returns 194
bytes2 = BinaryPeek2(flBuf,0)        ; 52418

I did not yet thought about the Little Endian format.

Best Regards Jan Willem 

[spl]

Is there a way you can provide a sample for the db? Microsoft still provides 32-bit Paradox ODBC (I just checked for Win10/11). 

[jwt]

Here is the tested code for my paradox TimeStamp conversion. It is not optimized yet.

#definefunction HexTimeStamp2ISODate(pString)  ; returns ISO Date "yyyyMMdd hh:mm:ss"
  if StrLen(pString) != 16 then
     return "error # 16 hex chars"
  endif
  DAGSEC = 86400 ; number of seconds in a day
  isodate = ""
  ; according to Knabe: Stored as Float value: Integer part as days since 0001-01-01 and fractional part as fraction of a day.
  ; Kevin Mitchell/Randy Beck: states only double precison
  ; this not correct: in this case it is stored in a LittleEndian format and the value is in milliseconds
  ; we convert the hex value to a floating point value
  ; pString is in LittleEndian format
  hexDate = StrLittle2BigEndian(16,pString) ; 8 bytes
  hdlBin = BinaryAlloc(8)
  BinaryPokeHex(hdlBin, 0, hexDate)
  floatDateRev = BinaryPeekFlt(hdlBin, 0)  ; in milliseconds
  BinaryFree(hdlBin)
  floatDateRev = floatDateRev/1000     ; convert to seconds
  if floatdateRev < 0 then
     floatDateRev = -1*floatDateRev
  endif
  days = int(floor(FloatDateRev/DAGSEC))
  if days < 1000 then
    return "error in calculating nr of days"
  endif
  seconds =  FloatDateRev mod days
  days = days - 1    ; in this we have to subtract one day to get the correct date
  decimals(0)
  newdate = timeAdd('0001:01:01:00:00:00','0000:00:%days%:00:00:00')
  newdate = StrSub(newdate, 1, 10)
  isodate = StrReplace(newdate, ":", "")
  hrs = int(floor(seconds/3600))
  secs = seconds mod 3600
  mins = int(floor(secs/60))  ; minutes
  secs = secs mod 60          ; seconds
  decimals(0)
  if hrs > 9 then
     strhrs = "%hrs%"
  else
     strhrs = "0%hrs%"
  endif
  if mins > 9 then
     strmin = "%mins%"
  else
     strmin = "0%mins%"
  endif
  if secs > 9 then
     strsec = "%secs%"
  else
     strsec = "0%secs%"
  endif
  isodate := " " : strhrs : strmin : strsec
  return isodate
#endfunction ; HexTimeStamp2ISODate(pString)

When my paradox exporting DB/Mb file combination is finished, I will publish an article and the code on my website, etc.

[spl]

In your last reply you wrote

; this not correct: in this case it is stored in a LittleEndian format and the value is in milliseconds

If that is the case, date could be returned as ticks and interpreted appropriately w/out going through binary code.

[EDIT]
and the ticks value between  01/01/0001 00:00 and 01/01/1601 00:00 is 504911232000000000 [as FromFileTime() is based on Gregorian Calendar].

[JTaylor]

For my own curiosity, and apologies if I am not seeing it, would you mind posting an example of the paradox timestamp and the correctly converted value?

Jim

[td]

I think the issue is mostly one of language (the spoken kind.)*

*I am not trying to be critical in any way. As most casual readers of this site know, I have a hard enough time with one language let alone juggling multiple languages.

[spl]

For my own curiosity, and apologies if I am not seeing it, would you mind posting an example of the paradox timestamp and the correctly converted value?

Jim

I played around with original hex string from the OP C2CCBE630B8D9C00. The decimal value exceeds the .NET Int64 max of 9223372036854775807, so assuming it was little endian reversed it to 00C9D8B036EBCC2C, converted to decimal and computed date with FromFileTime() - result was Sunday, January 14, 1781 1:42:46 PM. If correct, this would substantiate that the Paradox timestamp was stored as little endian as milliseconds/ticks. If not, back to your original curiosity as to providing an exact hex and converted date. And, yes, FromFileTime() can be used in WB, you can search it on this board.