pgsql/src/interfaces/jdbc/org/postgresql jdbc1 ... - Mailing list pgsql-committers

CVSROOT:    /home/projects/pgsql/cvsroot
Module name:    pgsql
Changes by:    momjian@hub.org    01/01/24 18:41:04

Modified files:
    src/interfaces/jdbc/org/postgresql/jdbc1: PreparedStatement.java
                                              ResultSet.java
    src/interfaces/jdbc/org/postgresql/jdbc2: PreparedStatement.java
                                              ResultSet.java

Log message:
    Attached is a revised patch that removes the static SimpleDateFormat
    objects that Thomas pointed out might be a problem.

    PPS.  I have included and updated the comments from the original patch
    request to reflect the changes made in this revised patch.

    > Attached is a set of patches for a couple of bugs dealing with
    > timestamps in JDBC.
    >
    > Bug#1) Incorrect timestamp stored in DB if client timezone different
    > than DB.
    > The buggy implementation of setTimestamp() in PreparedStatement simply
    > used the toString() method of the java.sql.Timestamp object to convert
    > to a string to send to the database.  The format of this is yyyy-MM-dd
    > hh:mm:ss.SSS which doesn't include any timezone information.  Therefore
    > the DB assumes its timezone since none is specified.  That is OK if the
    > timezone of the client and server are the same, however if they are
    > different the wrong timestamp is received by the server.  For example if
    > the client is running in timezone GMT and wants to send the timestamp
    > for noon to a server running in PST (GMT-8 hours), then the server will
    > receive 2000-01-12 12:00:00.0 and interprete it as 2000-01-12
    > 12:00:00-08 which is 2000-01-12 04:00:00 in GMT.  The fix is to send a
    > format to the server that includes the timezone offset.  For simplicity
    > sake the fix uses a SimpleDateFormat object with its timezone set to GMT
    > so that '+00' can be used as the timezone for postgresql.  This is done
    > as SimpleDateFormat doesn't support formating timezones in the way
    > postgresql expects.
    >
    > Bug#2) Incorrect handling of partial seconds in getting timestamps from
    > the DB
    >
    > When the SimpleDateFormat object parses a string with a format like
    > yyyy-MM-dd hh:mm:ss.SS it expects the fractional seconds to be three
    > decimal places (time precision in java is miliseconds = three decimal
    > places).  This seems like a bug in java to me, but it is unlikely to be
    > fixed anytime soon, so the postgresql code needed modification to
    > support the java behaviour.  So for example a string of '2000-01-12
    > 12:00:00.12-08' coming from the database was being converted to a
    > timestamp object with a value of 2000-01-12 12:00:00.012GMT-08:00.  The
    > fix was to check for a '.' in the string and if one is found append on
    > an extra zero to the fractional seconds part.
    >
    >
    > I also did some cleanup in ResultSet.getTimestamp().  This method has
    > had multiple patches applied some of which resulted in code that was no
    > longer needed.  For example the ISO timestamp format that postgresql
    > uses specifies the timezone as an offset like '-08'.  Code was added at
    > one point to convert the postgresql format to the java one which is
    > GMT-08:00, however the old code was left around which did nothing.  So
    > there was code that looked for yyyy-MM-dd hh:mm:sszzzzzzzzz and
    > yyyy-MM-dd hh:mm:sszzz.  This second format would never be encountered
    > because zzz (i.e. -08) would be converted into the former (also note
    > that the SimpleDateFormat object treats zzzzzzzzz and zzz the same, the
    > number of z's does not matter).
    >
    >
    > There was another problem/fix mentioned on the email lists today by
    > mcannon@internet.com which is also fixed by this patch:
    >
    > Bug#3) Fractional seconds lost when getting timestamp from the DB
    > A patch by Jan Thomea handled the case of yyyy-MM-dd hh:mm:sszzzzzzzzz
    > but not the fractional seconds version yyyy-MM-dd hh:mm:ss.SSzzzzzzzzz.
    > The code is fixed to handle this case as well.

    Barry Lind