Timestamp vs. Java Date/Timestamp

From: Andreas Reichel <andreas(at)manticore-projects(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Timestamp vs. Java Date/Timestamp
Date: 2013-02-05 05:47:07
Message-ID: 1360043227.14635.9.camel@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2013-02-05 14:42:29 Re: Timestamp vs. Java Date/Timestamp
Previous Message dmp 2013-02-04 17:11:44 Re: [BUGS]log can not be output when use DataSource