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

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@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
>
>