Timestamp vs. Java Date/Timestamp
От | Andreas Reichel |
---|---|
Тема | Timestamp vs. Java Date/Timestamp |
Дата | |
Msg-id | 1360043227.14635.9.camel@localhost обсуждение исходный текст |
Ответы |
Re: Timestamp vs. Java Date/Timestamp
Re: Timestamp vs. Java Date/Timestamp |
Список | 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
В списке pgsql-jdbc по дате отправления: