Re: Portable general timestamp format, not 2038-limited

From:

Owen Jacobson <angrybaldguy@gmail.com>

Newsgroups:

comp.programming,comp.lang.java.programmer

Date:

Fri, 22 Jun 2007 14:06:40 -0700

Message-ID:

<1182546400.132518.267260@i38g2000prf.googlegroups.com>

James Harris wrote:

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

Use your database's TIMESTAMP type (which is a standard SQL type) and
the java.sql.Timestamp class and call it a day. As far as I know the
various perl and python database libraries can cope with SQL
TIMESTAMPs too.

For postgresql, TIMESTAMP is internally an 8-byte value representing a
point in MJD, with millisecond precision and 4713 BC to 5874897 AD
range.

For mysql, TIMESTAMP is internally a time_t; this does give it the
same limitations as time_t on the platform it was compiled on (1970 to
2038 AD and 1 second resolution for 32-bit systems); you may consider
using DATETIME which the various MySQL drivers for different languages
all convert to timestamp equivalents. There are no built-in temporal
types for mysql with resolution better than 1 second, a problem which
is (as far as I know) unique to that database.