Обсуждение: Bug in TimestampUtils.java?

On 11/10/06, Richard Cook <awhig@yahoo.com> wrote:
>
> Hi All,
>
> I recently ran into a problem that was partially caused by some behaviour I
> found in TimestampUtils.java.  To me it does not seem right...
>
> I retrieved a Timestamp Column from Postgres as a Date. The data in the
> database had a timezone of GMT -0500, the current time was also in GMT
> -0500. If you look at the date that is returned from resultSet.getDate() on
> a Timestamp column, the timezone for the Date object is -0400. I would think
> that it should be -0500.

I suggest you to confirm it with your database server. Try developing the same
scenario without JDBC and see what you get because connectors are not
supposed to play with the data they get. It is the server who formats everything
before sending it to the client.

--Imad
www.EnterpriseDB.com

Richard Cook wrote:

> If you look at the source code for the postgres jdbc driver, it
> retrieves the column as a string, then uses the TimeStampUtils class to
> create a date or timestamp out of it. I think the driver is incorrectly
> setting the timezone when it creates the Date object.

I missed the original post here, but if you retrieve the result as a
String what do you get? (the exact value please) .. and how does that
compare to the Date you get?

-O

I discussed this with a colleague and he thinks this can be an issue of
daylight saving time. Does that make sense to you?

--Imad
www.EnterpriseDB.com


On 11/10/06, Richard Cook <awhig@yahoo.com> wrote:
>
> Imad,
>
> If you look at the source code for the postgres jdbc driver, it retrieves
> the column as a string, then uses the TimeStampUtils class to create a date
> or timestamp out of it. I think the driver is incorrectly setting the
> timezone when it creates the Date object.
>
>
> From AbstractJdbc2ResultSet.java....
>
>  public java.sql.Date getDate(int columnIndex) throws SQLException
>     {
>         return getDate(columnIndex, null);
>     }
>
>
>  public java.sql.Date getDate(int i, java.util.Calendar cal) throws
> SQLException
>     {
>         this.checkResultSet(i);
>
>         if (cal != null)
>             cal = (Calendar)cal.clone();
>
>         return connection.getTimestampUtils().toDate(cal, getString(i));
>     }
>
>
>
>
> ----- Original Message ----
> From: imad <immaad@gmail.com>
> To: Richard Cook <awhig@yahoo.com>
> Cc: pgsql-jdbc@postgresql.org
> Sent: Thursday, November 9, 2006 4:54:15 PM
> Subject: Re: [JDBC] Bug in TimestampUtils.java?
>
> On 11/10/06, Richard Cook <awhig@yahoo.com> wrote:
> >
> > Hi All,
> >
> > I recently ran into a problem that was partially caused by some behaviour
> I
> > found in TimestampUtils.java.  To me it does not seem right...
> >
> > I retrieved a Timestamp Column from Postgres as a Date. The data in the
> > database had a timezone of GMT -0500, the current time was also in GMT
> > -0500. If you look at the date that is returned from resultSet.getDate()
> on
> > a Timestamp column, the timezone for the Date object is -0400. I would
> think
> > that it should be -0500.
>
> I suggest you to confirm it with your database server. Try developing the
> same
> scenario without JDBC and see what you get because connectors are not
> supposed to play with the data they get. It is the server who formats
> everything
> before sending it to the client.
>
> --Imad
> www.EnterpriseDB.com
>
>

Richard Cook wrote:
> Oliver,
>
> Below is the debug output from TimestampUtils.java
>
> It parses the date string that says -05 into an -0400 Date object.
>
> Rich
>
>
> Date String: 2006-10-29 23:00:00-05
>
> debug output ---> Parsed date '2006-10-29 23:00:00-05' in zone
> America/New_York as 2006-10-29 AD 00:00:00 -0400 (millis=1162094400000)

I see no bug here.

(1) Date does not store a timezone offset, so timezone info cannot be
returned in the Date.
(2) getDate() returns a java.sql.Date which by definition must have no
hour/minute/second component (see javadoc)
(3) The driver uses the provided Calendar (or the local JVM timezone if
no Calendar was provided) to generate the Date it will return, in this
case America/New_York (-0400)
(4) The driver discards the hour/minute/second part of the value
returned by the server, and sets the year/month/day on the returned Date
using the Calendar from (3)

So what you end up with is a java.sql.Date for "2006-10-29 00:00:00" in
the -0400 timezone, which is exactly correct in terms of the semantics
of getDate, but isn't actually the same instant as the server returned.

As you have probably worked out by now, using getDate() on a full
timestamp type is a bit confusing because of the H/M/S truncation thing,
especially when timezones are involved. It's arguable whether the driver
should first convert to the timezone of the provided Calendar before
discarding the hour/minute/second component or not .. JDBC doesn't say
anything about how TIMESTAMP to DATE conversion should be done.

If you want to retrieve an instant in time complete with
hour/minute/second info, you should be using getTimestamp() not getDate().

Also note that "timestamp with time zone" does not actually *store* a
timezone, see the docs for details. JDBC doesn't give you any way I can
see to get at the timezone info returned by the server anyway.

-O