Re: Timestamp Conversion Woes Redux - Mailing list pgsql-jdbc

The only instance variable in the java.sql.Timestamp class is a Java
long primitive, which is the offset of the moment from 1970-01-01
00:00:00.0 GMT.  There is absolutely no other information carried in an
instance of this class.  The meaining of a Timestamp object is clear and
unambiguous.  By default, rendering a Timestamp to a String is done
using the default time zone for the JVM.  This code snippet:

    Timestamp ts = new Timestamp(0L);
    System.out.println(ts);

renders this on my console:

1969-12-31 18:00:00.0

because I'm in the U.S. Central time zone.  I could use a specific
Timezone object to render this as a string appropriate to any time zone
of my choice.  The questions would seem to be:

(1)  When storing a Timestamp object to a database column which is
defined as a timestamp with a time zone, what time zone should be used?
Regardless of the choice, the value in the column must represent the
same moment as the original Timestamp object.  It seems clear that some
methods allow you to specify a Calendar object for the sole purpose of
specifying the time zone, and that in the absence of that, the default
time zone of the JVM should be used.

(2)  When storing a Timestamp object to a database column which is
defined as a timestamp without a time zone, which time zone's local
representation of the Timestamp object should be used to convert the
Timestamp object from a moment to whatever the heck a timestamp without
a time zone is?  The rub is that the semantics of such a database column
are not obvious.  Does it represent the moment corresponding to the
given year, month, day, hour, minute, second, etc. in the server's time
zone, the client's time zone, GMT, or something else?  There are use
cases where each of these make sense, although the primary reason for
HAVING a timestamp without a timezone, would seem to be so that the
actual moment would be different for different clients.  (For example, a
database used in many time zones, which contained columns to configure
when late-night maintenance activities on client machines should occur.)
 It seems to me that specifying a time zone on database writes in this
case should cause the database representation of the moment represented
by the Timestamp object to be the date and time of that moment in the
specified time zone.

I can't think of anything in the ANSI SQL or JDBC specifications or
javadocs which provides any direction on what a timestamp without a time
zone represents; however, there are two compelling reasons to use the
time zone of the client:

(a)  The only reasonable use cases I can see for storing a timestamp
which does NOT represent a moment in time involve interpreting it as
different moments based on the client time zone.  This means that
software the is right and proper would want to use the local timezone,
at least for retrieval, and we all like symmetry, don't we?

(b)  The most common reason for using a timestamp without a timezone is
certainly that people are assuming that the whole system is based around
a single timezone which they never explicitly specify, and they don't
want to bother with it.  In this case, the client and server time zones
should match (or it was a very bad choice for data type).  If the time
zones DON'T match, we're dealing with a fundamentally screwed up
situation at that point -- the schema is fundamentally inappropriate for
the runtime environment.  It seems to me that allowing the client side
to specify a time zone when presenting the Timestamp to the database is
the reasonable way to go, with the default being the default time zone
for the JVM.

The solution to Christian's problem seems to me to be that he should
either get the timestamps from his source as String objects (using a
database server side function to convert them to the desired
character-based representation), and generate the values as literals in
loading the new database,

OR

he should ensure that his client and sever use the same time zone and
that the Timestamp objects actually represent the moments he cares
about.

Either should work.

-Kevin