Re: Timestamp Conversion Woes Redux

On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote:

> 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.
>
According to the JDBC API tutorial the time zone of the server
> (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
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>