Re: Timestamp without timezone issue

Chip Gobs <chip.gobs 'at' noaa.gov> writes:

> Let me be more clear about the update query that I quoted. It was
> created using the JDBC driver, which was adding the time zone
> information. I don't want any timezone information in my query. We
> are using the <timestamp without timezone> type and the time always
> represents UTC.

As I showed, there are implicit time zone operations, for example
when using Timestamp.valueOf(). If you don't want any time zone
information you have to be very careful with what methods you
chose.

I don't know how the timestamp value is transmitted from JDBC to
the database. I think that it is the date-time part of the
timestamp, at the JVM's timezone. In other words, if you use
Timestamp.valueOf() of a string, I think the string will make it
ok to the database. If you use new Timestamp(millis), then the
string will make it shifted by the time zone of the JVM (e.g. if
the millis is 10:00:00 UTC, then it will be 11:00:00+01 and the
timestamp in the database will be 11:00:00). All boils down to
what is the trustful source of your timestamps and in which time
zone do you want them "without time zone" (if the source is the
milliseconds since Epoch, you cannot free yourself from caring
about in which time zone the date-time will be represented).

> About updating key columns, I agree that it is neither necessary nor
> desirable to update the key columns.
> We have a code generator to create nearly all of our JDBC code and
> already have plans to change it to update only non-key columns. That
> said, bugs are rare using the generated code.
> But changing that won't solve my problem of having <timestamp without
> timezone> column values altered by the driver, whose behavior seems to
> have changed with the newer version. I have 2 non-key timestamp
> columns (that I omitted for brevity) that need to be updated with the
> correct time.

I have made a couple of tests between postgresql backends 7.4.5
and 8.2.5 (always using postgresql-8.2-504.jdbc3.jar, which was
adviced with 7.4 already). I don't see a difference appearing
actually between each version of postgresql. I use this table:

Table "public.tz_test"
Column | Type | Modifiers
------------------+-----------------------------+-----------
tz | text |
date_string | text |
date_timestamp | timestamp without time zone |
date_timestamptz | timestamp with time zone |

Creation of the timestamp object is:

String input = "2007-12-05 10:00:00.000000000";
Timestamp ts = Timestamp.valueOf( input );

then I insert TimeZone.getDefault().getID() into tz,
ts.toString() into date_string, and ts into the two timestamp
columns; one time with JVM's timezone at Europe/Zurich, and one
time at UTC.

Selecting the rows from the 7.4.5 and the 8.2.5 databases both
yield the same results:

tz | date_string | date_timestamp | date_timestamptz
---------------+-----------------------+---------------------+------------------------
Europe/Zurich | 2007-12-05 10:00:00.0 | 2007-12-05 10:00:00 | 2007-12-05 09:00:00+00
UTC | 2007-12-05 10:00:00.0 | 2007-12-05 10:00:00 | 2007-12-05 10:00:00+00

> So my question is, other than my workaround:
>
> SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> formatter.setTimeZone(TimeZone.getTimeZone("UTC"));
> dateTimeString = formatter.format(new java.util.Date(timeInMillis));
>
> Timestamp timestamp = Timestamp.valueOf(dateTimeString);
> statement.setTimestamp(index, timestamp);
>
> How do I tell the driver to leave my <timestamp without
> timezone> values without a timezone attached to them?
>
>
> Why does
>
> Timestamp timestamp = new Timestamp(timeInMillis);
> statement.setTimestamp(index, timestamp);
>
> not work? The timeInMillis value is supposed to represent millis
> since Jan 1, 1970 00:00:00 GMT.

I think the response may be related to the content of my second
paragraph at the top of this message, though I don't know how
timestamps are transmitted to the database.

My advice would be to use timestamp with time zone, if possible.
It makes timestamps non ambiguous and saves from problems when
changing the system time zone, or needing to present the
timestamps in other time zones.

I think there might be some bugs or different implementations
in previous versions of the JDBC driver, which explain what
you're seeing (after all, as Dave Cramer says, if the JDBC spec
doesn't know about timestamp without time zones, you're bound to
implementation details which may be different over the time).

Anyway, in my opinion, if you don't want / can't change your
column types, I suggest to avoid using the milliseconds epoch,
but always use date-time representations; that way, the driver
will probably transmit the same date-time representation to the
database. Let us know how it turns out.

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36