PDA

View Full Version : internal storage format of TIME

Goh
04-10-01, 15:22
Upon importing Paradox table into Access, field of date type converted properly, but field of time did not. Time shows as a 9-digit 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 9-digit number to hour:min:sec format.

Does anybody know the internal storage format of the TIME in paradox?

Thanks
Goh
Bertil Isberg
04-10-01, 17:20
It's number of milliseconds from a specified date. I'm not sure which one though.

___________________
Bertil Isberg CTECH
Joseph Misko
04-10-01, 20:49
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))
Liz
04-10-01, 21:33
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.
Rick Kelly
04-11-01, 04:59
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 (http://www.crooit.com/)