Time Format, stored as single

[jan@BRU]

Hello,
I have a problem, which seems banale, but I can't get it to work... any help is appreciated.

I have a (large) table (>1 m records), which contains several fields, storing time (and time zone bias) information. In order to reduce overall size of the database, I have not used date/Time fields for the storage of the time information (8 bytes). Rather I use Single (4 Byte), and still store the time in the same format, as a date/time field would (i.e. as fractions of a day).

This works fine for all storage, calculation and display purposes. I can (e.g.) display the fields using the 'short time' format and they will display correctly as hh:nn, and i can even have negative values for (e.g.) time zone differences and they still display correctly, if I use a ('+hh:nn,-hh:nn,00:00) time format (something I can't do with date/time fields).

Now HERE is my problem: using these fields in a form (no matter whether this is a continuous, datasheet or single form), I can see them, but CANNOT enter a new time, in this field, using any kind of 'normal' time form (i.e. something like 22:45 or 08:23) it will require me to input the decimal values instead (i.e. 0.9479167 or 0.3493056) - this is obviously not convenient. I have been fumbling around with "Input formats", but still can't get it to work.

Anybody can help?

Many thanks.

Best,

Jan

 

[gemma-the-husky]

it seems strange to worry about 4 bytes of storage, and losing all the intrinsic date and time manipulation functionality.

anyway, to use your mechanism, you would now have to enter the time as a string (text box) - check that it is formatted correctly, and IS a valid 24 hour time - maybe allow 4 numeric digits, or five with a colon. so you would have to disallow 25:30 or 12:70 etc.

then you should be able to use some simple arithmetic to parse and convert this into a decimal time, such as you are storing.

as I say, if you use a date-time type, all this is done for you.