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  (Dave Cramer <pg@fastcrypt.com>)
Re: Timestamp vs. Java Date/Timestamp  (Thomas Kellerer <spam_eater@gmx.net>)
Список 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 по дате отправления:

Предыдущее
От: dmp
Дата:
Сообщение: Re: [BUGS]log can not be output when use DataSource
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Timestamp vs. Java Date/Timestamp