Re: Timestamp vs. Java Date/Timestamp

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
>