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

Dave,

my previous post was not correct, but I finally found the culprit:

For any reason we use bindString:

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

which formats Date into a String, but without information on the time:

public synchronized String toString(Calendar cal, Date x) {
        if (cal == null)
            cal = defaultCal;

        cal.setTime(x);
        sbuf.setLength(0);

        if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
            sbuf.append("infinity");
        } else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
            sbuf.append("-infinity");
        } else {
// the date only but no time
            appendDate(sbuf, cal);
//
            appendEra(sbuf, cal);
            appendTimeZone(sbuf, cal);
        }

        showString("date", cal, x, sbuf.toString());

        return sbuf.toString();
    }

When I modified this function into:

public synchronized String toString(Calendar cal, Date x) {
        if (cal == null)
            cal = defaultCal;

        cal.setTime(x);
        sbuf.setLength(0);

        if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
            sbuf.append("infinity");
        } else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
            sbuf.append("-infinity");
        } else {
            appendDate(sbuf, cal);

// obey the time too as java.util.Date holds the time
            sbuf.append(" ");
            appendTime(sbuf, cal, 0);
//
            appendEra(sbuf, cal);
            appendTimeZone(sbuf, cal);
        }

        showString("date", cal, x, sbuf.toString());

        return sbuf.toString();
    }

everything works as expected and also everything else still worked well.
(My program uses a lot of date/time conversions so I have some
confidence).

Now there are two questions please:

a) would you like to apply this small change because java.util.Date
holds time information so we should obey it

b) why is there all this Date/String conversion instead just using
millis/Long? I expected using setDate() gives better performance than
handing over Strings but now I found that it does exactly the same and
in an unexpected way?

Best regards
Andreas




On Tue, 2013-02-05 at 09:42 -0500, Dave Cramer wrote:
> Andreas,
>
>
> What are you using to setTimestamp in the prepared statement ? setDate
> or setTimestamp ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
> On Tue, Feb 5, 2013 at 12:47 AM, Andreas Reichel
> <andreas@manticore-projects.com> wrote:
>         Dear List,
>
>         the last day I had a hard time figuring out how to hand over
>         timestamps
>         using prepared statements.
>
>         The table looks like this:
>
>         trader=# \d trader.tickdata
>                            Table "trader.tickdata"
>               Column       |            Type             | Modifiers
>         -------------------+-----------------------------+-----------
>          id_instrument     | smallint                    | not null
>          id_stock_exchange | smallint                    | not null
>          timestamp         | timestamp without time zone | not null
>          price             | double precision            | not null
>
>
>         Now I would like to retrieve ticks using a prepared statement
>         like this:
>
>         -- GET TICKDATA
>         select
>                 t1.id_instrument,
>                 t1.id_stock_exchange,
>                 t1."timestamp",
>                 t1.price,
>                 coalesce(t2.quantity,0) quantity
>         from
>                 trader.tickdata t1
>                 left join trader.volumedata t2
>                         ON (t1.id_instrument=t2.id_instrument AND
>         t1.id_stock_exchange=t2.id_stock_exchange AND
>         t1."timestamp"=t2."timestamp")
>         where
>                 t1.id_instrument= ?
>                 AND t1.id_stock_exchange= ?
>                 --careful with TIMEZONE here!
>                 AND t1."timestamp">= ?
>                 AND t1."timestamp"<= ?
>         ORDER BY t1."timestamp" ASC;
>
>         If I hand over java.util.Date or java.sql.Date or
>         java.sql.Timestamp the
>         query will be executed but returns the wrong number of
>         records;
>
>         However, if I change the query into:
>         -- GET TICKDATA
>         select
>                 t1.id_instrument,
>                 t1.id_stock_exchange,
>                 t1."timestamp",
>                 t1.price,
>                 coalesce(t2.quantity,0) quantity
>         from
>                 trader.tickdata t1
>                 left join trader.volumedata t2
>                         ON (t1.id_instrument=t2.id_instrument AND
>         t1.id_stock_exchange=t2.id_stock_exchange AND
>         t1."timestamp"=t2."timestamp")
>         where
>                 t1.id_instrument= ?
>                 AND t1.id_stock_exchange= ?
>                 --careful with TIMEZONE here!
>                 AND t1."timestamp">= cast(? as timestamp)
>                 AND t1."timestamp"<= cast(? as timestamp)
>         ORDER BY t1."timestamp" ASC;
>
>         and hand over a formated date "yyyy-MM-dd HH:mm:ss" it works
>         correctly.
>         Now I have on simple questions please:
>
>         What is the correct way to hand over a Java Date parameter
>         (avoiding the
>         double String manipulation)?
>
>         Thank you and best regards!
>         Andreas
>
>
>
>
>         --
>         Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-jdbc
>
>