| 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: | Whole Thread | Raw Message | 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
| 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 |