Thread: Timestamp without timezone issue
We recently changed from using PostgreSQL 7.4.8 to 8.2.5 and switched
the JDBC driver to the 8.2.506 version from the 74.215 version. We are
and have been using build 1.5.0_04-b05 of the J2SE since before our
Postgres version change.
After switching, we started receiving large numbers of errors in the
postgres error log file. These are unique constraint errors on UPDATEs,
when we are not actually trying to change any of the key columns. The
errors are reported as follows (irrelevant non-key columns have been
removed for clarity):
Nov 30 13:25:12 machinename postgres[29003]: [13-1] ERROR: duplicate
key violates unique constraint "arealobs_pk"
Nov 30 13:25:12 machinename postgres[29003]: [13-2] STATEMENT: UPDATE
arealobs SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM', extremum =
'Z', obstime = '2007-11-30
Nov 30 13:25:12 machinename postgres[29003]: [13-3]
10:00:00.000000-06', value = 0.0, Nov 30 13:25:12 machinename
postgres[29003]: [13-4] WHERE lid = 'NAME1' AND pe
Nov 30 13:25:12 machinename postgres[29003]: [13-5] = 'PP' AND dur =
'1001' AND ts = 'PM' AND extremum = 'Z' AND obstime = '2007-11-30 16:00:00'
The key columns on this table are lid, pe, dur, ts, extremum and obstime.
Notice the (-06 US Central time) time zone information in the log
message.
The column obstime is of type timestamp without timezone. After using
psql to experiment, it appears that the -06 is being ignored and the
time in the value assignment part of the update statement is being
considered as 10:00:00 UTC instead of 16:00:00 UTC.
A workaround is to use:
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);
The following did not work:
TimeZone tz = TimeZone.getTimeZone("UTC");
Calendar cal = Calendar.getInstance(tz);
Timestamp timestamp = new Timestamp(timeInMillis);
statement.setTimestamp(index, timestamp , cal);
Neither did:
Timestamp timestamp = new Timestamp(timeInMillis);
statement.setTimestamp(index, timestamp);
Is this a known issue, a new one, or was I doing something wrong?
Thanks,
Chip Gobs
Chip Gobs <chip.gobs 'at' noaa.gov> writes:
> We recently changed from using PostgreSQL 7.4.8 to 8.2.5 and switched
> the JDBC driver to the 8.2.506 version from the 74.215 version. We
> are and have been using build 1.5.0_04-b05 of the J2SE since before
> our Postgres version change.
>
> After switching, we started receiving large numbers of errors in the
> postgres error log file. These are unique constraint errors on
> UPDATEs, when we are not actually trying to change any of the key
> columns. The errors are reported as follows (irrelevant non-key
> columns have been removed for clarity):
> Nov 30 13:25:12 machinename postgres[29003]: [13-1] ERROR: duplicate
> key violates unique constraint "arealobs_pk"
> Nov 30 13:25:12 machinename postgres[29003]: [13-2] STATEMENT:
> UPDATE arealobs SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
> extremum = 'Z', obstime = '2007-11-30
> Nov 30 13:25:12 machinename postgres[29003]: [13-3]
> 10:00:00.000000-06', value = 0.0, Nov 30 13:25:12 machinename
> postgres[29003]: [13-4] WHERE lid = 'NAME1' AND pe
> Nov 30 13:25:12 machinename postgres[29003]: [13-5] = 'PP' AND dur =
> 1001' AND ts = 'PM' AND extremum = 'Z' AND obstime = '2007-11-30
> 16:00:00'
Rewriting the query for increased readability:
UPDATE arealobs
SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
extremum = 'Z', obstime = '2007-11-30 10:00:00.000000-06', value = 0.0
WHERE lid = 'NAME1' AND pe = 'PP' AND dur = '1001' AND ts = 'PM'
AND extremum = 'Z' AND obstime = '2007-11-30 16:00:00'
> The key columns on this table are lid, pe, dur, ts, extremum and obstime.
That is "arealobs_pk" I suppose?
My first question would be of logics: a primary key normally
designates a unique way of identifying an entry (a row) in a
table; therefore, it's normally not desirable to update the
primary key columns, when you are just updating the data relative
to a specific entry designed by the values of the primary key
columns (you're just updating the "value" column, if I guess
correctly). Your application would probably be more logical and
less bound to bugs if you just update the value column here?
> Notice the (-06 US Central time) time zone information in the log
> message.
> The column obstime is of type timestamp without timezone. After
> using psql to experiment, it appears that the -06 is being ignored
> and the time in the value assignment part of the update statement is
> being considered as 10:00:00 UTC instead of 16:00:00 UTC.
The fact that -06 is ignored when working with timestamp without
time zone seems normal; quoting the documentation: In a literal
that has been decided to be timestamp without time zone,
PostgreSQL will silently ignore any time zone indication. That
is, the resulting value is derived from the date/time fields in
the input value, and is not adjusted for time zone.
If you want the timezone to be used for properly offseting the
timestamp in input, you should use timestamp with time zone
datatype.
> A workaround is to use:
>
> 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);
Personally, I avoid using Timestamp.valueOf, because it uses the
JVM's timezone to compute the actual timestamp's value. This code:
System.out.println( "jvm's timezone: " + TimeZone.getDefault().getID() );
String input = "2007-12-05 10:00:00.000000000";
Timestamp ts = Timestamp.valueOf( input );
System.out.println( input + "'s is " + ts.getTime() + " milliseconds since January 1, 1970, 00:00:00 GMT" );
outputs that result:
- with the default timezone of my system:
jvm's timezone: Europe/Zurich
2007-12-05 10:00:00.000000000 is parsed to be 1196845200000 milliseconds since January 1, 1970, 00:00:00 GMT
- in UTC:
jvm's timezone: UTC
2007-12-05 10:00:00.000000000 is parsed to be 1196848800000 milliseconds since January 1, 1970, 00:00:00 GMT
Instead, I always parse a date-time input using date formatters
(with date formatters at the configured time zone of the
application, actually).
I think your workaround may work because the timezone of your
system is -06. The actual timestamp object is shifted because of
Timestamp.valueOf's behaviour. If this is what you want, you
should rather use a date formatter at the desired time zone.
For the record: we always use timestamp with time zone in our
database, to avoid time zone manipulation problems and
confidently be able to change the used timezone in the
application (or for users), whatever timezone is used internally
by the database and/or by the system.
--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland
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.
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.
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.
Thanks,
Chip
Guillaume Cottenceau wrote:
> Chip Gobs <chip.gobs 'at' noaa.gov> writes:
>
>
>> We recently changed from using PostgreSQL 7.4.8 to 8.2.5 and switched
>> the JDBC driver to the 8.2.506 version from the 74.215 version. We
>> are and have been using build 1.5.0_04-b05 of the J2SE since before
>> our Postgres version change.
>>
>> After switching, we started receiving large numbers of errors in the
>> postgres error log file. These are unique constraint errors on
>> UPDATEs, when we are not actually trying to change any of the key
>> columns. The errors are reported as follows (irrelevant non-key
>> columns have been removed for clarity):
>> Nov 30 13:25:12 machinename postgres[29003]: [13-1] ERROR: duplicate
>> key violates unique constraint "arealobs_pk"
>> Nov 30 13:25:12 machinename postgres[29003]: [13-2] STATEMENT:
>> UPDATE arealobs SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
>> extremum = 'Z', obstime = '2007-11-30
>> Nov 30 13:25:12 machinename postgres[29003]: [13-3]
>> 10:00:00.000000-06', value = 0.0, Nov 30 13:25:12 machinename
>> postgres[29003]: [13-4] WHERE lid = 'NAME1' AND pe
>> Nov 30 13:25:12 machinename postgres[29003]: [13-5] = 'PP' AND dur =
>> 1001' AND ts = 'PM' AND extremum = 'Z' AND obstime = '2007-11-30
>> 16:00:00'
>>
>
> Rewriting the query for increased readability:
>
> UPDATE arealobs
> SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
> extremum = 'Z', obstime = '2007-11-30 10:00:00.000000-06', value = 0.0
> WHERE lid = 'NAME1' AND pe = 'PP' AND dur = '1001' AND ts = 'PM'
> AND extremum = 'Z' AND obstime = '2007-11-30 16:00:00'
>
>
>> The key columns on this table are lid, pe, dur, ts, extremum and obstime.
>>
>
> That is "arealobs_pk" I suppose?
>
> My first question would be of logics: a primary key normally
> designates a unique way of identifying an entry (a row) in a
> table; therefore, it's normally not desirable to update the
> primary key columns, when you are just updating the data relative
> to a specific entry designed by the values of the primary key
> columns (you're just updating the "value" column, if I guess
> correctly). Your application would probably be more logical and
> less bound to bugs if you just update the value column here?
>
>
>> Notice the (-06 US Central time) time zone information in the log
>> message.
>> The column obstime is of type timestamp without timezone. After
>> using psql to experiment, it appears that the -06 is being ignored
>> and the time in the value assignment part of the update statement is
>> being considered as 10:00:00 UTC instead of 16:00:00 UTC.
>>
>
> The fact that -06 is ignored when working with timestamp without
> time zone seems normal; quoting the documentation: In a literal
> that has been decided to be timestamp without time zone,
> PostgreSQL will silently ignore any time zone indication. That
> is, the resulting value is derived from the date/time fields in
> the input value, and is not adjusted for time zone.
>
> If you want the timezone to be used for properly offseting the
> timestamp in input, you should use timestamp with time zone
> datatype.
>
>
>> A workaround is to use:
>>
>> 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);
>>
>
> Personally, I avoid using Timestamp.valueOf, because it uses the
> JVM's timezone to compute the actual timestamp's value. This code:
>
> System.out.println( "jvm's timezone: " + TimeZone.getDefault().getID() );
> String input = "2007-12-05 10:00:00.000000000";
> Timestamp ts = Timestamp.valueOf( input );
> System.out.println( input + "'s is " + ts.getTime() + " milliseconds since January 1, 1970, 00:00:00 GMT" );
>
> outputs that result:
>
> - with the default timezone of my system:
>
> jvm's timezone: Europe/Zurich
> 2007-12-05 10:00:00.000000000 is parsed to be 1196845200000 milliseconds since January 1, 1970, 00:00:00 GMT
>
> - in UTC:
>
> jvm's timezone: UTC
> 2007-12-05 10:00:00.000000000 is parsed to be 1196848800000 milliseconds since January 1, 1970, 00:00:00 GMT
>
> Instead, I always parse a date-time input using date formatters
> (with date formatters at the configured time zone of the
> application, actually).
>
> I think your workaround may work because the timezone of your
> system is -06. The actual timestamp object is shifted because of
> Timestamp.valueOf's behaviour. If this is what you want, you
> should rather use a date formatter at the desired time zone.
>
> For the record: we always use timestamp with time zone in our
> database, to avoid time zone manipulation problems and
> confidently be able to change the used timezone in the
> application (or for users), whatever timezone is used internally
> by the database and/or by the system.
>
>
On 5-Dec-07, at 8:13 AM, Chip Gobs wrote:
> 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.
>
Unfortunately the JDBC spec does not understand timestamp without
timezone. A timestamp object is extended from Date which has time zone
information.
> 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.
>
> 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.
>
>
Timestamp has timezone information in it...
> Thanks,
>
> Chip
>
>
> Guillaume Cottenceau wrote:
>> Chip Gobs <chip.gobs 'at' noaa.gov> writes:
>>
>>
>>> We recently changed from using PostgreSQL 7.4.8 to 8.2.5 and
>>> switched
>>> the JDBC driver to the 8.2.506 version from the 74.215 version. We
>>> are and have been using build 1.5.0_04-b05 of the J2SE since before
>>> our Postgres version change.
>>>
>>> After switching, we started receiving large numbers of errors in the
>>> postgres error log file. These are unique constraint errors on
>>> UPDATEs, when we are not actually trying to change any of the key
>>> columns. The errors are reported as follows (irrelevant non-key
>>> columns have been removed for clarity):
>>> Nov 30 13:25:12 machinename postgres[29003]: [13-1] ERROR:
>>> duplicate
>>> key violates unique constraint "arealobs_pk"
>>> Nov 30 13:25:12 machinename postgres[29003]: [13-2] STATEMENT:
>>> UPDATE arealobs SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
>>> extremum = 'Z', obstime = '2007-11-30
>>> Nov 30 13:25:12 machinename postgres[29003]: [13-3]
>>> 10:00:00.000000-06', value = 0.0, Nov 30 13:25:12 machinename
>>> postgres[29003]: [13-4] WHERE lid = 'NAME1' AND pe
>>> Nov 30 13:25:12 machinename postgres[29003]: [13-5] = 'PP' AND
>>> dur =
>>> 1001' AND ts = 'PM' AND extremum = 'Z' AND obstime = '2007-11-30
>>> 16:00:00'
>>>
>>
>> Rewriting the query for increased readability:
>>
>> UPDATE arealobs
>> SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
>> extremum = 'Z', obstime = '2007-11-30 10:00:00.000000-06',
>> value = 0.0
>> WHERE lid = 'NAME1' AND pe = 'PP' AND dur = '1001' AND ts = 'PM'
>> AND extremum = 'Z' AND obstime = '2007-11-30 16:00:00'
>>
>>
>>> The key columns on this table are lid, pe, dur, ts, extremum and
>>> obstime.
>>>
>>
>> That is "arealobs_pk" I suppose?
>>
>> My first question would be of logics: a primary key normally
>> designates a unique way of identifying an entry (a row) in a
>> table; therefore, it's normally not desirable to update the
>> primary key columns, when you are just updating the data relative
>> to a specific entry designed by the values of the primary key
>> columns (you're just updating the "value" column, if I guess
>> correctly). Your application would probably be more logical and
>> less bound to bugs if you just update the value column here?
>>
>>
>>> Notice the (-06 US Central time) time zone information in the
>>> log
>>> message.
>>> The column obstime is of type timestamp without timezone. After
>>> using psql to experiment, it appears that the -06 is being ignored
>>> and the time in the value assignment part of the update statement
>>> is
>>> being considered as 10:00:00 UTC instead of 16:00:00 UTC.
>>>
>>
>> The fact that -06 is ignored when working with timestamp without
>> time zone seems normal; quoting the documentation: In a literal
>> that has been decided to be timestamp without time zone,
>> PostgreSQL will silently ignore any time zone indication. That
>> is, the resulting value is derived from the date/time fields in
>> the input value, and is not adjusted for time zone.
>>
>> If you want the timezone to be used for properly offseting the
>> timestamp in input, you should use timestamp with time zone
>> datatype.
>>
>>
>>> A workaround is to use:
>>>
>>> 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);
>>>
>>
>> Personally, I avoid using Timestamp.valueOf, because it uses the
>> JVM's timezone to compute the actual timestamp's value. This code:
>>
>> System.out.println( "jvm's timezone: " +
>> TimeZone.getDefault().getID() );
>> String input = "2007-12-05 10:00:00.000000000";
>> Timestamp ts = Timestamp.valueOf( input );
>> System.out.println( input + "'s is " + ts.getTime() + "
>> milliseconds since January 1, 1970, 00:00:00 GMT" );
>>
>> outputs that result:
>>
>> - with the default timezone of my system:
>>
>> jvm's timezone: Europe/Zurich
>> 2007-12-05 10:00:00.000000000 is parsed to be 1196845200000
>> milliseconds since January 1, 1970, 00:00:00 GMT
>>
>> - in UTC:
>>
>> jvm's timezone: UTC
>> 2007-12-05 10:00:00.000000000 is parsed to be 1196848800000
>> milliseconds since January 1, 1970, 00:00:00 GMT
>> Instead, I always parse a date-time input using date
>> formatters
>> (with date formatters at the configured time zone of the
>> application, actually).
>>
>> I think your workaround may work because the timezone of your
>> system is -06. The actual timestamp object is shifted because of
>> Timestamp.valueOf's behaviour. If this is what you want, you
>> should rather use a date formatter at the desired time zone.
>>
>> For the record: we always use timestamp with time zone in our
>> database, to avoid time zone manipulation problems and
>> confidently be able to change the used timezone in the
>> application (or for users), whatever timezone is used internally
>> by the database and/or by the system.
>>
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
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
On Tue, 4 Dec 2007, Chip Gobs wrote:
> We recently changed from using PostgreSQL 7.4.8 to 8.2.5 and switched the
> JDBC driver to the 8.2.506 version from the 74.215 version. We are and have
> been using build 1.5.0_04-b05 of the J2SE since before our Postgres version
> change.
>
> The column obstime is of type timestamp without timezone. After using psql
> to experiment, it appears that the -06 is being ignored and the time in the
> value assignment part of the update statement is being considered as
> 10:00:00 UTC instead of 16:00:00 UTC.
>
> The following did not work:
>
> TimeZone tz = TimeZone.getTimeZone("UTC");
> Calendar cal = Calendar.getInstance(tz);
> Timestamp timestamp = new Timestamp(timeInMillis);
> statement.setTimestamp(index, timestamp , cal);
>
Could you be more clear on why this doesn't work? The attached test case
seems like it is working as you desire.
Kris Jurka