Re: Timestamp vs. Java Date/Timestamp - Mailing list pgsql-jdbc

Dave, my apologies for a rather long posting, perhaps I found what
happens:

//--------------------------------------------------------------------
// Step 1: hand over a Date calls setDate without Calendar, good:

 case Types.DATE:
                if (in instanceof java.sql.Date)
                    setDate(parameterIndex, (java.sql.Date)in);
                else
                {
                    java.sql.Date tmpd;
                    if (in instanceof java.util.Date) {
                        tmpd = new
java.sql.Date(((java.util.Date)in).getTime());
                    } else {
                        tmpd = connection.getTimestampUtils().toDate(null,
in.toString());
                    }
                    setDate(parameterIndex, tmpd);
                }
                break;


//--------------------------------------------------------------------
// Step 2: setDate calls setDate with Calendar==NULL:

    public void setDate(int parameterIndex, java.sql.Date x) throws
SQLException
    {
        setDate(parameterIndex, x, null);
    }

//--------------------------------------------------------------------
// Step 3: setDate with Calendar==NULL calls TimeStampUtils with empty
TimeZone:

    public void setDate(int i, java.sql.Date d, java.util.Calendar cal)
throws SQLException
    {
        checkClosed();

        if (d == null)
        {
            setNull(i, Types.DATE);
            return;
        }

        if (connection.binaryTransferSend(Oid.DATE)) {
            byte[] val = new byte[4];
            TimeZone tz = cal != null ? cal.getTimeZone() : null;
            connection.getTimestampUtils().toBinDate(tz, val, d);
            preparedParameters.setBinaryParameter(i, val, Oid.DATE);
            return;
        }

        if (cal != null)
            cal = (Calendar)cal.clone();

        // We must use UNSPECIFIED here, or inserting a
Date-with-timezone into a
        // timestamptz field does an unexpected rotation by the server's
TimeZone:
        //
        // We want to interpret 2005/01/01 with calendar +0100 as
        // "local midnight in +0100", but if we go via date it
interprets it
        // as local midnight in the server's timezone:

        // template1=# select '2005-01-01+0100'::timestamptz;
        //       timestamptz
        // ------------------------
        //  2005-01-01 02:00:00+03
        // (1 row)

        // template1=# select '2005-01-01+0100'::date::timestamptz;
        //       timestamptz
        // ------------------------
        //  2005-01-01 00:00:00+03
        // (1 row)

        bindString(i, connection.getTimestampUtils().toString(cal, d),
Oid.UNSPECIFIED);
    }

//--------------------------------------------------------------------
// Step 4: when TimeZone is empty, the defaultTZ is used --> Why?! I
have a field which is WITHOUT Timezone for good reasons

    public void toBinDate(TimeZone tz, byte[] bytes, Date value) throws
PSQLException {
        long millis = value.getTime();

        if (tz == null) {
            tz = defaultTz;
        }
        millis += tz.getOffset(millis);

        long secs = toPgSecs(millis / 1000);
        ByteConverter.int4(bytes, 0, (int) (secs / 86400));
    }


I would like to suggest to modify the toBinDate() and to add
tz.getOffset(millis) ONLY if a Timezone was specified, but not when
NULL:

    public void toBinDate(TimeZone tz, byte[] bytes, Date value) throws
PSQLException {
        long millis = value.getTime();

        if (tz != null) {
            millis += tz.getOffset(millis);
        }

        long secs = toPgSecs(millis / 1000);
        ByteConverter.int4(bytes, 0, (int) (secs / 86400));
    }

What do you think?

Best regards
Andreas