I have a requirement to store timestamps in a database. Simple enough you might think but finding a suitably general format is not easy. The specifics are
1) subsecond resolution - milliseconds or, preferably, more detailed 2) not bounded by Unix timestamp 2038 limit 3) readable in Java 4) writable portably in Perl which seems to mean that 64-bit values are out 5) readable and writable in Python 6) storable in a free database - Postgresql/MySQL The formats provided by the two database systems are such as 8-byte or 12-byte values which, even if I could get Perl to work with I guess it would be messy. Keeping to 32-bit values should give me portability and be easy enough to work with without obscuring the program logic. Since 32 bits of microseconds is less than 50 days I have to store two 32-bit values. How to split them? The option I favour at the moment is to split days and parts of days like this: a) store, as a 32-bit number, days since a virtual year zero (there is no year zero in common era time <http://en.wikipedia.org/wiki/ Common_Era>). This allows over five million years plus and minus. Still not completely general, I know. b) store parts of days as another 32-bit value. Its range would have to go to 86401 seconds - the number of seconds in a leap day. This means each 'tick' would be around 21 microseconds. For regularity I could make the ticks 25 microseconds so there would be 40,000 in a second and 3,456,000,000 in a day; and, finally, the counter could tick about 5 hours into the next day if not caught. Any thoughts on a better way to do this? (Please reply-all. Thanks). -- James -- http://mail.python.org/mailman/listinfo/python-list
