Re: Timestamp vs. Java Date/Timestamp

Hi Dave,

I tried setObject() handing over java.util.Date or java.sql.Date.
Furthermore I tried setDate() (which enforces java.sql.Date) and
setTimestamp (which enforces java,sql.Timestamp). It all failed the same
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
>
>