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/13 00:18:05

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 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.

    Bug#3) Performance problems

    In fixing the above two bugs, I noticed some things that could be
    improved.  In PreparedStatement.setTimestamp(),
    PreparedStatement.setDate(), ResultSet.getTimestamp(), and
    ResultSet.getDate() these methods were creating a new SimpleDateFormat
    object everytime they were called.  To avoid this unnecessary object
    creation overhead, I changed the code to use static variables for
    keeping a single instance of the needed formating objects.
    Also the code used the + operator for string concatenation.  As everyone
    should know this is very inefficient and the use of StringBuffers is
    prefered.

    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#4) 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