Re: Timestamp vs. Java Date/Timestamp

Andreas this does not pass the built in tests. run ant test to see

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Tue, Feb 5, 2013 at 11:11 PM, Andreas Reichel <
andreas(at)manticore-projects(dot)com> wrote:

> 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(at)manticore-projects(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> >
>
>
>