Results 1 to 5 of 5
Thread: internal storage format of TIME

041001, 14:22 #1Goh Guest
Answered: internal storage format of TIME
Upon importing Paradox table into Access, field of date type converted properly, but field of time did not. Time shows as a 9digit number like "394864800" with last two digits always being 0's. "394864800" is supposed to correspond to 11:04:38 but I cannot figure out how to convert this 9digit number to hour:min:sec format.
Does anybody know the internal storage format of the TIME in paradox?
Thanks
Goh

Rick Kelly
Posted by"With a number in excess of the maximum for a time value, paradox could only possibly interpret it as a datetime type which is stored as number of milliseconds for each day plus the number of milliseconds since midnight. With the number provided that yields:
394864800.0 / 86400000.0 dropping the remainder = 4 = Jan 4, 0001
394864800.0  4.0 * 86400000.0 = 49,264,800 = 1:41:04 PM
If it is supposed to be 11:04:38 PM (83,078,000) or 11:04:38 AM (39,878,000) I've not a clue what Access has done. Maybe an export from Paradox to an intermediate format like ASCII would get around this.
___________________
Rick Kelly
Crooit Software"

041001, 16:20 #2Bertil Isberg Guest
It's number of milliseconds from a specified date. I'm not sure which one though.
___________________
Bertil Isberg CTECH

041001, 19:49 #3Joseph Misko Guest
I believe it holds the number of milliseconds since midnight. Therefore, the minimum value is zero, which corresponds to midnight 0:00:00, and the maximum value is one less than 24 hours * 60 minutes * 60 seconds * 1,000 milliseconds = 86,400,000, which would be 23:59:59 and 999 milliseconds (one millisecond before midnight). If this is correct, you should be able to divide the value by 1,000 and drop the milliseconds and just work with hours, minutes, and seconds (wrapped up in the resultant integer). Use mod and div operations with 60 and 24 to get the values you need.
___________________
Joseph Misko
Just my format("E" + chr(36) + "L1,W.2", (number(chr(45) + chr(50)) / 100))

041001, 20:33 #4Liz Guest
Joseph,
Paradox does it the way you describe, but that number (the example from the initial post) is larger than the largest possible value (though, if you plug it in, and cast it as time, it comes out as 13:??:?? (I forget the minutes and seconds) which would indicate 13:??:?? on some day in the future (i.e. x days and 13 hours since midnight)).
I cannot figure out where Access is coming up with that number.
Liz
I believe it holds the number of milliseconds since midnight. Therefore, the minimum value is zero, which corresponds to midnight 0:00:00, and the maximum value is one less than 24 hours * 60 minutes * 60 seconds * 1,000 milliseconds = 86,400,000, which would be 23:59:59 and 999 milliseconds (one millisecond before midnight). If this is correct, you should be able to divide the value by 1,000 and drop the milliseconds and just work with hours, minutes, and seconds (wrapped up in the resultant integer). Use mod and div operations with 60 and 24 to get the values you need.

041101, 03:59 #5Rick Kelly Guest
With a number in excess of the maximum for a time value, paradox could only possibly interpret it as a datetime type which is stored as number of milliseconds for each day plus the number of milliseconds since midnight. With the number provided that yields:
394864800.0 / 86400000.0 dropping the remainder = 4 = Jan 4, 0001
394864800.0  4.0 * 86400000.0 = 49,264,800 = 1:41:04 PM
If it is supposed to be 11:04:38 PM (83,078,000) or 11:04:38 AM (39,878,000) I've not a clue what Access has done. Maybe an export from Paradox to an intermediate format like ASCII would get around this.
___________________
Rick Kelly
Crooit Software