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
The script is very rough and makes several assumptions but it will, hopefully, get you started in the correct direction.
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)
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.
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.
Quote from: jwt on December 19, 2024, 02:10:44 AMThanks, 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.
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)
exitIf you do not reverse the byte order you get the number 0.00000.
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.
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
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).
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.
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].
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 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.
Quote from: JTaylor on December 23, 2024, 06:51:29 AMFor 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.