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).