Thread: Timestamp vs. Java Date/Timestamp
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
Andreas,
What are you using to setTimestamp in the prepared statement ? setDate or setTimestamp ?
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
Andreas Reichel, 05.02.2013 06:47: > Dear List, > > the last day I had a hard time figuring out how to hand over timestamps > using prepared statements. > > What is the correct way to hand over a Java Date parameter (avoiding the > double String manipulation)? Pass an instance of java.sql.Timestamp using the setTimestamp() method.
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 > >
Dave, my apologies for a rather long posting, perhaps I found what
happens:
//--------------------------------------------------------------------
// Step 1: hand over a Date calls setDate without Calendar, good:
case Types.DATE:
if (in instanceof java.sql.Date)
setDate(parameterIndex, (java.sql.Date)in);
else
{
java.sql.Date tmpd;
if (in instanceof java.util.Date) {
tmpd = new
java.sql.Date(((java.util.Date)in).getTime());
} else {
tmpd = connection.getTimestampUtils().toDate(null,
in.toString());
}
setDate(parameterIndex, tmpd);
}
break;
//--------------------------------------------------------------------
// Step 2: setDate calls setDate with Calendar==NULL:
public void setDate(int parameterIndex, java.sql.Date x) throws
SQLException
{
setDate(parameterIndex, x, null);
}
//--------------------------------------------------------------------
// Step 3: setDate with Calendar==NULL calls TimeStampUtils with empty
TimeZone:
public void setDate(int i, java.sql.Date d, java.util.Calendar cal)
throws SQLException
{
checkClosed();
if (d == null)
{
setNull(i, Types.DATE);
return;
}
if (connection.binaryTransferSend(Oid.DATE)) {
byte[] val = new byte[4];
TimeZone tz = cal != null ? cal.getTimeZone() : null;
connection.getTimestampUtils().toBinDate(tz, val, d);
preparedParameters.setBinaryParameter(i, val, Oid.DATE);
return;
}
if (cal != null)
cal = (Calendar)cal.clone();
// We must use UNSPECIFIED here, or inserting a
Date-with-timezone into a
// timestamptz field does an unexpected rotation by the server's
TimeZone:
//
// We want to interpret 2005/01/01 with calendar +0100 as
// "local midnight in +0100", but if we go via date it
interprets it
// as local midnight in the server's timezone:
// template1=# select '2005-01-01+0100'::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 02:00:00+03
// (1 row)
// template1=# select '2005-01-01+0100'::date::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 00:00:00+03
// (1 row)
bindString(i, connection.getTimestampUtils().toString(cal, d),
Oid.UNSPECIFIED);
}
//--------------------------------------------------------------------
// Step 4: when TimeZone is empty, the defaultTZ is used --> Why?! I
have a field which is WITHOUT Timezone for good reasons
public void toBinDate(TimeZone tz, byte[] bytes, Date value) throws
PSQLException {
long millis = value.getTime();
if (tz == null) {
tz = defaultTz;
}
millis += tz.getOffset(millis);
long secs = toPgSecs(millis / 1000);
ByteConverter.int4(bytes, 0, (int) (secs / 86400));
}
I would like to suggest to modify the toBinDate() and to add
tz.getOffset(millis) ONLY if a Timezone was specified, but not when
NULL:
public void toBinDate(TimeZone tz, byte[] bytes, Date value) throws
PSQLException {
long millis = value.getTime();
if (tz != null) {
millis += tz.getOffset(millis);
}
long secs = toPgSecs(millis / 1000);
ByteConverter.int4(bytes, 0, (int) (secs / 86400));
}
What do you think?
Best regards
Andreas
Dave,
my previous post was not correct, but I finally found the culprit:
For any reason we use bindString:
bindString(i, connection.getTimestampUtils().toString(cal, d),
Oid.UNSPECIFIED);
which formats Date into a String, but without information on the time:
public synchronized String toString(Calendar cal, Date x) {
if (cal == null)
cal = defaultCal;
cal.setTime(x);
sbuf.setLength(0);
if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
sbuf.append("infinity");
} else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
sbuf.append("-infinity");
} else {
// the date only but no time
appendDate(sbuf, cal);
//
appendEra(sbuf, cal);
appendTimeZone(sbuf, cal);
}
showString("date", cal, x, sbuf.toString());
return sbuf.toString();
}
When I modified this function into:
public synchronized String toString(Calendar cal, Date x) {
if (cal == null)
cal = defaultCal;
cal.setTime(x);
sbuf.setLength(0);
if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
sbuf.append("infinity");
} else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
sbuf.append("-infinity");
} else {
appendDate(sbuf, cal);
// obey the time too as java.util.Date holds the time
sbuf.append(" ");
appendTime(sbuf, cal, 0);
//
appendEra(sbuf, cal);
appendTimeZone(sbuf, cal);
}
showString("date", cal, x, sbuf.toString());
return sbuf.toString();
}
everything works as expected and also everything else still worked well.
(My program uses a lot of date/time conversions so I have some
confidence).
Now there are two questions please:
a) would you like to apply this small change because java.util.Date
holds time information so we should obey it
b) why is there all this Date/String conversion instead just using
millis/Long? I expected using setDate() gives better performance than
handing over Strings but now I found that it does exactly the same and
in an unexpected 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
>
>
Andreas this does not pass the built in tests. run ant test to see
On Tue, Feb 5, 2013 at 11:11 PM, Andreas Reichel <andreas@manticore-projects.com> wrote:
Dave,
my previous post was not correct, but I finally found the culprit:
For any reason we use bindString:
bindString(i, connection.getTimestampUtils().toString(cal, d),
Oid.UNSPECIFIED);
which formats Date into a String, but without information on the time:
public synchronized String toString(Calendar cal, Date x) {
if (cal == null)
cal = defaultCal;
cal.setTime(x);
sbuf.setLength(0);
if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
sbuf.append("infinity");
} else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
sbuf.append("-infinity");
} else {
// the date only but no time
appendDate(sbuf, cal);
//
appendEra(sbuf, cal);
appendTimeZone(sbuf, cal);
}
showString("date", cal, x, sbuf.toString());
return sbuf.toString();
}
When I modified this function into:
public synchronized String toString(Calendar cal, Date x) {
if (cal == null)
cal = defaultCal;
cal.setTime(x);
sbuf.setLength(0);
if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
sbuf.append("infinity");
} else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
sbuf.append("-infinity");
} else {
appendDate(sbuf, cal);
// obey the time too as java.util.Date holds the time
sbuf.append(" ");
appendTime(sbuf, cal, 0);
//
appendEra(sbuf, cal);
appendTimeZone(sbuf, cal);
}
showString("date", cal, x, sbuf.toString());
return sbuf.toString();
}
everything works as expected and also everything else still worked well.
(My program uses a lot of date/time conversions so I have some
confidence).
Now there are two questions please:
a) would you like to apply this small change because java.util.Date
holds time information so we should obey it
b) why is there all this Date/String conversion instead just using
millis/Long? I expected using setDate() gives better performance than
handing over Strings but now I found that it does exactly the same and
in an unexpected 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
>
>
Andreas,
I guess I should have replied back sooner, but it looked like the
problem you were having and the question you originally asked was
handled. Perhaps I can get some feedback to the original question
before, Dave, the JDBC is modified to effect the Date formmatting.
QUESTION: What is the proper way to handle the TIMESTAMP type in
PostgreSQL with a prepare statement? NOT TIMESTAMPTZ.
Without all this parsing of date and time with string.
Implying you are using java.util.Date in your app.
First the a DATE type in the database is not the same as a java.util.Date
in Java. The later does hold a time, and though the java.sql.Date is
define generally as a thinnly wrapped java.util.Date the last time
I checked NO database returns time with their DATE types. So with
this statement I'm questioning the change Andrea you have requested,
but do not see a problem with the patch, beside as already noted
by Dave the failing of the test routine.
Now one of my projects works with several database including
PostgreSQL and I have found the problems that occur most fequently
are when trying to adapt the app. to a multitute of temporal types.
Example:
DATE
TIME
TIMETZ
DATETIME
TIMESTAMP
TIMESTAMPTZ
TIMESTAMPLTZ
YEAR
So I looked at specifically how I have handle TIMESTAMP types
in PostgreSQL with Preparedstatements and this is the solution
I used. Though it may not be the proper way, it does seem
to work. Note this approach can also be used with TIMESTAMPTZ
also by modifying the SimpleDateFormat and allows using
different DATE formats as illusttrated.
SimpleDateFormat timeStampFormat;
java.sql.Timestamp dateTimeValue;
java.util.Date dateParse;
timeStampFormat = new SimpleDateFormat("MM-dd-yyyy" + " HH:mm:ss");
dateParse = timeStampFormat.parse("12-30-2008 15:16:08");
dateTimeValue = new java.sql.Timestamp(dateParse.getTime());
prepared_sqlStatement.setTimestamp(i, dateTimeValue);
Perhaps this may help.
danap.
Dave Cramer wrote:
>
> Andreas this does not pass the built in tests. run ant test to see
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Tue, Feb 5, 2013 at 11:11 PM, Andreas Reichel
> <andreas@manticore-projects.com <mailto:andreas@manticore-projects.com>>
> wrote:
>
> Dave,
>
> my previous post was not correct, but I finally found the culprit:
>
> For any reason we use bindString:
>
> bindString(i, connection.getTimestampUtils().toString(cal, d),
> Oid.UNSPECIFIED);
>
> which formats Date into a String, but without information on the time:
>
> public synchronized String toString(Calendar cal, Date x) {
> if (cal == null)
> cal = defaultCal;
>
> cal.setTime(x);
> sbuf.setLength(0);
>
> if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
> sbuf.append("infinity");
> } else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
> sbuf.append("-infinity");
> } else {
> // the date only but no time
> appendDate(sbuf, cal);
> //
> appendEra(sbuf, cal);
> appendTimeZone(sbuf, cal);
> }
>
> showString("date", cal, x, sbuf.toString());
>
> return sbuf.toString();
> }
>
> When I modified this function into:
>
> public synchronized String toString(Calendar cal, Date x) {
> if (cal == null)
> cal = defaultCal;
>
> cal.setTime(x);
> sbuf.setLength(0);
>
> if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY) {
> sbuf.append("infinity");
> } else if (x.getTime() == PGStatement.DATE_NEGATIVE_INFINITY) {
> sbuf.append("-infinity");
> } else {
> appendDate(sbuf, cal);
>
> // obey the time too as java.util.Date holds the time
> sbuf.append(" ");
> appendTime(sbuf, cal, 0);
> //
> appendEra(sbuf, cal);
> appendTimeZone(sbuf, cal);
> }
>
> showString("date", cal, x, sbuf.toString());
>
> return sbuf.toString();
> }
>
> everything works as expected and also everything else still worked well.
> (My program uses a lot of date/time conversions so I have some
> confidence).
>
> Now there are two questions please:
>
> a) would you like to apply this small change because java.util.Date
> holds time information so we should obey it
>
> b) why is there all this Date/String conversion instead just using
> millis/Long? I expected using setDate() gives better performance than
> handing over Strings but now I found that it does exactly the same and
> in an unexpected 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
> <mailto: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 <mailto:pgsql-jdbc@postgresql.org>)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> >
>
>
>
> Laurent Schweizer <laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
>
> > I have an issue with special character like é.
>
> > I have as server postgres 9.2, I have created a new DB , encoding
> > utf8
> >
> > Client is a very simple test class that:
> > 1) update a varchar value
> > 2) read the same value and print them
> >
> > If I update the varchar with a special character like “é” the
> > value in the DB is correct ( I check them with another software )
> > but when I read them from my simple java class the value is not
> > correct and the é is converted in é
> >
> > I have added to the connection string the option:
> > ?useUnicode=true&characterEncoding=utf8
> >
> > And if I do a : "SHOW client_encoding;” I get UTF8
>
> It is behaving as though the client is using a character encoding
> other than UTF8 -- some sort of 8-bit encoding, probably. You must
> set client_encoding to match.
>
> -Kevin
Hello Laruent,
I have tested the following method with the URL parameters you indicated
with PostgreSQL 9.0.1 and the latest driver. Both on a linux and windows
systems with the same result of the A and the Acute Latin e properly
displaying in a system.out and the frame. I suppose it could be modified
slightly to also check and update rather than an insert.
danap.
private void testInsertUTF(Connection con)
{
// Method Instances
String sqlStatementString;
Statement sqlStatement;
PreparedStatement pstmt;
ResultSet rs;
try
{
sqlStatement = con.createStatement();
con.setAutoCommit(false);
sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
sqlStatement.execute(sqlStatementString);
sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
sqlStatement.execute(sqlStatementString);
pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES (?), (?)");
pstmt.setString(1, "\u0041"); // A
pstmt.setString(2, "\u00E9"); // Acute Latin e
pstmt.execute();
sqlStatementString = "SELECT * FROM jdbc_demo";
sqlStatement.execute(sqlStatementString);
rs = sqlStatement.executeQuery(sqlStatementString);
JPanel panel = new JPanel();
while (rs.next())
{
String dataString = rs.getString("col");
System.out.println("col:" + dataString);
panel.add(new JLabel(dataString));
}
rs.close();
JFrame frame = new JFrame();
frame.getContentPane().add(panel);
frame.setSize(200, 200);
frame.setVisible(true);
sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
sqlStatement.execute(sqlStatementString);
sqlStatement.close();
pstmt.close();
con.setAutoCommit(true);
}
catch (SQLException sqle)
{
System.out.println("SQL Exeception" + sqle);
}
}
> Laurent Schweizer <laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
>
> > I have an issue with special character like é.
>
> > I have as server postgres 9.2, I have created a new DB , encoding
> > utf8
> >
> > Client is a very simple test class that:
> > 1) update a varchar value
> > 2) read the same value and print them
> >
> > If I update the varchar with a special character like “é” the
> > value in the DB is correct ( I check them with another software )
> > but when I read them from my simple java class the value is not
> > correct and the é is converted in é
> >
> > I have added to the connection string the option:
> > ?useUnicode=true&characterEncoding=utf8
> >
> > And if I do a : "SHOW client_encoding;” I get UTF8
>
> It is behaving as though the client is using a character encoding
> other than UTF8 -- some sort of 8-bit encoding, probably. You must
> set client_encoding to match.
>
> -Kevin
Hello Laruent,
I have tested the following method with the URL parameters you indicated
with PostgreSQL 9.0.1 and the latest driver. Both on a linux and windows
systems with the same result of the A and the Acute Latin e properly
displaying in a system.out and the frame. I suppose it could be modified
slightly to also check and update rather than an insert.
danap.
private void testInsertUTF(Connection con)
{
// Method Instances
String sqlStatementString;
Statement sqlStatement;
PreparedStatement pstmt;
ResultSet rs;
try
{
sqlStatement = con.createStatement();
con.setAutoCommit(false);
sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
sqlStatement.execute(sqlStatementString);
sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
sqlStatement.execute(sqlStatementString);
pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES (?), (?)");
pstmt.setString(1, "\u0041"); // A
pstmt.setString(2, "\u00E9"); // Acute Latin e
pstmt.execute();
sqlStatementString = "SELECT * FROM jdbc_demo";
sqlStatement.execute(sqlStatementString);
rs = sqlStatement.executeQuery(sqlStatementString);
JPanel panel = new JPanel();
while (rs.next())
{
String dataString = rs.getString("col");
System.out.println("col:" + dataString);
panel.add(new JLabel(dataString));
}
rs.close();
JFrame frame = new JFrame();
frame.getContentPane().add(panel);
frame.setSize(200, 200);
frame.setVisible(true);
sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
sqlStatement.execute(sqlStatementString);
sqlStatement.close();
pstmt.close();
con.setAutoCommit(true);
}
catch (SQLException sqle)
{
System.out.println("SQL Exeception" + sqle);
}
}
Hello,
I see that you directly convert special character , the problem is that
with my application data are inserted with another process .
When they insert the data in the DB all is ok. I have only an issue with
JDBC to get them correctly.
Laurent
-----Message d'origine-----
De : pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp
Envoyé : samedi 9 février 2013 02:42
À : laurent.schweizer@peoplefone.com; pgsql-jdbc@postgresql.org
Objet : Re: [JDBC] é converted in é
> Laurent Schweizer <laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
>
> > I have an issue with special character like é.
>
> > I have as server postgres 9.2, I have created a new DB , encoding > >
utf8 > > > > Client is a very simple test class that:
> > 1) update a varchar value
> > 2) read the same value and print them
> >
> > If I update the varchar with a special character like é the > >
value in the DB is correct ( I check them with another software ) > > but
when I read them from my simple java class the value is not > > correct
and the é is converted in é > > > > I have added to the connection string
the option:
> > ?useUnicode=true&characterEncoding=utf8
> >
> > And if I do a : "SHOW client_encoding; I get UTF8 > > It is
behaving as though the client is using a character encoding > other than
UTF8 -- some sort of 8-bit encoding, probably. You must > set
client_encoding to match.
>
> -Kevin
Hello Laruent,
I have tested the following method with the URL parameters you indicated
with PostgreSQL 9.0.1 and the latest driver. Both on a linux and windows
systems with the same result of the A and the Acute Latin e properly
displaying in a system.out and the frame. I suppose it could be modified
slightly to also check and update rather than an insert.
danap.
private void testInsertUTF(Connection con)
{
// Method Instances
String sqlStatementString;
Statement sqlStatement;
PreparedStatement pstmt;
ResultSet rs;
try
{
sqlStatement = con.createStatement();
con.setAutoCommit(false);
sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
sqlStatement.execute(sqlStatementString);
sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
sqlStatement.execute(sqlStatementString);
pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES (?),
(?)");
pstmt.setString(1, "\u0041"); // A
pstmt.setString(2, "\u00E9"); // Acute Latin e
pstmt.execute();
sqlStatementString = "SELECT * FROM jdbc_demo";
sqlStatement.execute(sqlStatementString);
rs = sqlStatement.executeQuery(sqlStatementString);
JPanel panel = new JPanel();
while (rs.next())
{
String dataString = rs.getString("col");
System.out.println("col:" + dataString);
panel.add(new JLabel(dataString));
}
rs.close();
JFrame frame = new JFrame();
frame.getContentPane().add(panel);
frame.setSize(200, 200);
frame.setVisible(true);
sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
sqlStatement.execute(sqlStatementString);
sqlStatement.close();
pstmt.close();
con.setAutoCommit(true);
}
catch (SQLException sqle)
{
System.out.println("SQL Exeception" + sqle);
}
}
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes
to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Hello Laurent,
Use the Method provided to compare the two inserts methods, the one
I have demostrated and your process. Reply back with the modified
method, code, so that we may have a way of duplicating the error you
are describing.
I modified the Method this morning and used a update on the extended
ASCII chararcter and was still able to to a system.out and placement
in JLabel to observe the correct results.
If you drive to a garage and ask the mechanic to fix your lights on
your car because they do not work and he/she turns them on and they
work, how do you expect the mechanic to help?
Provide sample code demostrating your issue and perhaps help can be
more forth comming.
danap.
Laurent Schweizer wrote:
> Hello,
>
> I see that you directly convert special character , the problem is that
> with my application data are inserted with another process .
>
> When they insert the data in the DB all is ok. I have only an issue with
> JDBC to get them correctly.
>
> Laurent
>
>
>
>
> -----Message d'origine-----
> De : pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp
> Envoyé : samedi 9 février 2013 02:42
> À : laurent.schweizer@peoplefone.com; pgsql-jdbc@postgresql.org
> Objet : Re: [JDBC] é converted in é
>
> > Laurent Schweizer<laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
> >
> > > I have an issue with special character like é.
> >
> > > I have as server postgres 9.2, I have created a new DB , encoding> >
> utf8> > > > Client is a very simple test class that:
> > > 1) update a varchar value
> > > 2) read the same value and print them
> > >
> > > If I update the varchar with a special character like “é” the> >
> value in the DB is correct ( I check them with another software )> > but
> when I read them from my simple java class the value is not> > correct
> and the é is converted in é> > > > I have added to the connection string
> the option:
> > > ?useUnicode=true&characterEncoding=utf8
> > >
> > > And if I do a : "SHOW client_encoding;” I get UTF8> > It is
> behaving as though the client is using a character encoding> other than
> UTF8 -- some sort of 8-bit encoding, probably. You must> set
> client_encoding to match.
> >
> > -Kevin
>
> Hello Laruent,
>
> I have tested the following method with the URL parameters you indicated
> with PostgreSQL 9.0.1 and the latest driver. Both on a linux and windows
> systems with the same result of the A and the Acute Latin e properly
> displaying in a system.out and the frame. I suppose it could be modified
> slightly to also check and update rather than an insert.
>
> danap.
>
> private void testInsertUTF(Connection con)
> {
> // Method Instances
> String sqlStatementString;
> Statement sqlStatement;
> PreparedStatement pstmt;
> ResultSet rs;
>
> try
> {
> sqlStatement = con.createStatement();
> con.setAutoCommit(false);
>
> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
> sqlStatement.execute(sqlStatementString);
>
> sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
> sqlStatement.execute(sqlStatementString);
>
> pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES (?),
> (?)");
> pstmt.setString(1, "\u0041"); // A
> pstmt.setString(2, "\u00E9"); // Acute Latin e
> pstmt.execute();
>
> sqlStatementString = "SELECT * FROM jdbc_demo";
> sqlStatement.execute(sqlStatementString);
>
> rs = sqlStatement.executeQuery(sqlStatementString);
>
> JPanel panel = new JPanel();
>
> while (rs.next())
> {
> String dataString = rs.getString("col");
> System.out.println("col:" + dataString);
> panel.add(new JLabel(dataString));
> }
> rs.close();
>
> JFrame frame = new JFrame();
> frame.getContentPane().add(panel);
> frame.setSize(200, 200);
> frame.setVisible(true);
>
> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
> sqlStatement.execute(sqlStatementString);
>
> sqlStatement.close();
> pstmt.close();
> con.setAutoCommit(true);
> }
> catch (SQLException sqle)
> {
> System.out.println("SQL Exeception" + sqle);
> }
> }
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
Dear all,
Bellow I have attached the code that I use to do the test, as you can see it's very simple.
I just do the test on windows (via Eclipse) and encoding is OK !
When I run same class on Linux , in command line, the encoding is not OK.
Did I need to set some specific parameters on linux ?
Regards
Laurent
import java.io.UnsupportedEncodingException;
import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.charset.Charset;
import java.sql.*;
public class Testing {
Connection conn;
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
new Testing();
}
public Testing ()
{
try
{
Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://95.128.80.38/testchar?useUnicode=true&characterEncoding=utf8";
conn = DriverManager.getConnection(url, "postgres", "");
doTests();
conn.close();
}
catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
catch (InstantiationException ex) {System.err.println(ex.getMessage());}
catch (SQLException ex) {System.err.println(ex.getMessage());}
}
private void doTests()
{
doSelectTest();
}
private void doSelectTest()
{
// String query1 = "SET client_encoding = 'LATIN9';";
String query = "SELECT input FROM test ";
try
{
// Statement st2 = conn.createStatement();
// st2.execute(query1);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next())
{
String s = rs.getString(1);
System.out.println("val:"+s );
}
}
catch (SQLException ex)
{
System.err.println(ex.getMessage());
}
/*
String queryup = "UPDATE test set input ='snom 320 é' WHERE id=1";
try
{
Statement st = conn.createStatement();
st.execute(queryup);
}
catch (SQLException ex)
{
System.err.println(ex.getMessage());
}*/
}
-----Message d'origine-----
De : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp
Envoyé : samedi 9 février 2013 17:55
À : Laurent Schweizer; pgsql-jdbc@postgresql.org
Objet : Re: [JDBC] é converted in é
Hello Laurent,
Use the Method provided to compare the two inserts methods, the one I have demostrated and your process. Reply back
withthe modified method, code, so that we may have a way of duplicating the error you are describing.
I modified the Method this morning and used a update on the extended ASCII chararcter and was still able to to a
system.outand placement in JLabel to observe the correct results.
If you drive to a garage and ask the mechanic to fix your lights on your car because they do not work and he/she turns
themon and they work, how do you expect the mechanic to help?
Provide sample code demostrating your issue and perhaps help can be more forth comming.
danap.
Laurent Schweizer wrote:
> Hello,
>
> I see that you directly convert special character , the problem is
> that with my application data are inserted with another process .
>
> When they insert the data in the DB all is ok. I have only an issue
> with JDBC to get them correctly.
>
> Laurent
>
>
>
>
> -----Message d'origine-----
> De : pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp Envoyé :
> samedi 9 février 2013 02:42 À : laurent.schweizer@peoplefone.com;
> pgsql-jdbc@postgresql.org Objet : Re: [JDBC] é converted in é
>
> > Laurent Schweizer<laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
> >
> > > I have an issue with special character like é.
> >
> > > I have as server postgres 9.2, I have created a new DB ,
> encoding> >
> utf8> > > > Client is a very simple test class that:
> > > 1) update a varchar value
> > > 2) read the same value and print them
> > >
> > > If I update the varchar with a special character like “é”
> the> > value in the DB is correct ( I check them with another
> software )> > but when I read them from my simple java class the value is not> > correct
> and the é is converted in é> > > > I have added to the connection string
> the option:
> > > ?useUnicode=true&characterEncoding=utf8
> > >
> > > And if I do a : "SHOW client_encoding;” I get UTF8> > It is
> behaving as though the client is using a character encoding> other
> than
> UTF8 -- some sort of 8-bit encoding, probably. You must> set
> client_encoding to match.
> >
> > -Kevin
>
> Hello Laruent,
>
> I have tested the following method with the URL parameters you
> indicated with PostgreSQL 9.0.1 and the latest driver. Both on a linux
> and windows systems with the same result of the A and the Acute Latin
> e properly displaying in a system.out and the frame. I suppose it
> could be modified slightly to also check and update rather than an insert.
>
> danap.
>
> private void testInsertUTF(Connection con)
> {
> // Method Instances
> String sqlStatementString;
> Statement sqlStatement;
> PreparedStatement pstmt;
> ResultSet rs;
>
> try
> {
> sqlStatement = con.createStatement();
> con.setAutoCommit(false);
>
> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
> sqlStatement.execute(sqlStatementString);
>
> sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
> sqlStatement.execute(sqlStatementString);
>
> pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES
> (?), (?)");
> pstmt.setString(1, "\u0041"); // A
> pstmt.setString(2, "\u00E9"); // Acute Latin e
> pstmt.execute();
>
> sqlStatementString = "SELECT * FROM jdbc_demo";
> sqlStatement.execute(sqlStatementString);
>
> rs = sqlStatement.executeQuery(sqlStatementString);
>
> JPanel panel = new JPanel();
>
> while (rs.next())
> {
> String dataString = rs.getString("col");
> System.out.println("col:" + dataString);
> panel.add(new JLabel(dataString));
> }
> rs.close();
>
> JFrame frame = new JFrame();
> frame.getContentPane().add(panel);
> frame.setSize(200, 200);
> frame.setVisible(true);
>
> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
> sqlStatement.execute(sqlStatementString);
>
> sqlStatement.close();
> pstmt.close();
> con.setAutoCommit(true);
> }
> catch (SQLException sqle)
> {
> System.out.println("SQL Exeception" + sqle);
> }
> }
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Hello Laurent,
I indicated that I have demostrated with the Test Method I posting a
functional sample code that does not produce the result you are indicating
on both a Windows and Linux platform.
The code you posted is useless. Show us your insertion process with
a valid test case like I have demostrated.
danap.
Laurent Schweizer wrote:
> Dear all,
>
> Bellow I have attached the code that I use to do the test, as you can see it's very simple.
>
> I just do the test on windows (via Eclipse) and encoding is OK !
> When I run same class on Linux , in command line, the encoding is not OK.
>
> Did I need to set some specific parameters on linux ?
>
> Regards
>
> Laurent
>
>
>
> import java.io.UnsupportedEncodingException;
> import java.nio.ByteBuffer;
> import java.nio.CharBuffer;
> import java.nio.charset.Charset;
> import java.sql.*;
> public class Testing {
>
> Connection conn;
> /**
> * @param args
> */
> public static void main(String[] args) {
> // TODO Auto-generated method stub
> new Testing();
>
> }
>
>
>
> public Testing ()
> {
> try
> {
>
> Class.forName("org.postgresql.Driver").newInstance();
> String url = "jdbc:postgresql://95.128.80.38/testchar?useUnicode=true&characterEncoding=utf8";
> conn = DriverManager.getConnection(url, "postgres", "");
> doTests();
> conn.close();
> }
> catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
> catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
> catch (InstantiationException ex) {System.err.println(ex.getMessage());}
> catch (SQLException ex) {System.err.println(ex.getMessage());}
> }
>
> private void doTests()
> {
> doSelectTest();
>
>
> }
>
> private void doSelectTest()
> {
>
> // String query1 = "SET client_encoding = 'LATIN9';";
>
>
> String query = "SELECT input FROM test ";
> try
> {
>
> // Statement st2 = conn.createStatement();
> // st2.execute(query1);
> Statement st = conn.createStatement();
> ResultSet rs = st.executeQuery(query);
> while (rs.next())
> {
> String s = rs.getString(1);
> System.out.println("val:"+s );
>
> }
> }
> catch (SQLException ex)
> {
> System.err.println(ex.getMessage());
> }
>
>
> /*
> String queryup = "UPDATE test set input ='snom 320 é' WHERE id=1";
> try
> {
> Statement st = conn.createStatement();
> st.execute(queryup);
>
> }
> catch (SQLException ex)
> {
> System.err.println(ex.getMessage());
> }*/
>
> }
>
> -----Message d'origine-----
> De : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp
> Envoyé : samedi 9 février 2013 17:55
> À : Laurent Schweizer; pgsql-jdbc@postgresql.org
> Objet : Re: [JDBC] é converted in é
>
> Hello Laurent,
>
> Use the Method provided to compare the two inserts methods, the one I have demostrated and your process. Reply back
withthe modified method, code, so that we may have a way of duplicating the error you are describing.
>
> I modified the Method this morning and used a update on the extended ASCII chararcter and was still able to to a
system.outand placement in JLabel to observe the correct results.
>
> If you drive to a garage and ask the mechanic to fix your lights on your car because they do not work and he/she
turnsthem on and they work, how do you expect the mechanic to help?
>
> Provide sample code demostrating your issue and perhaps help can be more forth comming.
>
> danap.
>
> Laurent Schweizer wrote:
>> Hello,
>>
>> I see that you directly convert special character , the problem is
>> that with my application data are inserted with another process .
>>
>> When they insert the data in the DB all is ok. I have only an issue
>> with JDBC to get them correctly.
>>
>> Laurent
>>
>>
>> -----Message d'origine-----
>> De : pgsql-jdbc-owner@postgresql.org
>> [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp Envoyé :
>> samedi 9 février 2013 02:42 À : laurent.schweizer@peoplefone.com;
>> pgsql-jdbc@postgresql.org Objet : Re: [JDBC] é converted in é
>>
>> > Laurent Schweizer<laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
>> >
>> > > I have an issue with special character like é.
>> >
>> > > I have as server postgres 9.2, I have created a new DB ,
>> encoding> >
>> utf8> > > > Client is a very simple test class that:
>> > > 1) update a varchar value
>> > > 2) read the same value and print them
>> > >
>> > > If I update the varchar with a special character like “é”
>> the> > value in the DB is correct ( I check them with another
>> software )> > but when I read them from my simple java class the value is not> > correct
>> and the é is converted in é> > > > I have added to the connection string
>> the option:
>> > > ?useUnicode=true&characterEncoding=utf8
>> > >
>> > > And if I do a : "SHOW client_encoding;” I get UTF8> > It is
>> behaving as though the client is using a character encoding> other
>> than
>> UTF8 -- some sort of 8-bit encoding, probably. You must> set
>> client_encoding to match.
>> >
>> > -Kevin
>>
>> Hello Laruent,
>>
>> I have tested the following method with the URL parameters you
>> indicated with PostgreSQL 9.0.1 and the latest driver. Both on a linux
>> and windows systems with the same result of the A and the Acute Latin
>> e properly displaying in a system.out and the frame. I suppose it
>> could be modified slightly to also check and update rather than an insert.
>>
>> danap.
>>
>> private void testInsertUTF(Connection con)
>> {
>> // Method Instances
>> String sqlStatementString;
>> Statement sqlStatement;
>> PreparedStatement pstmt;
>> ResultSet rs;
>>
>> try
>> {
>> sqlStatement = con.createStatement();
>> con.setAutoCommit(false);
>>
>> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
>> sqlStatement.execute(sqlStatementString);
>>
>> sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
>> sqlStatement.execute(sqlStatementString);
>>
>> pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES
>> (?), (?)");
>> pstmt.setString(1, "\u0041"); // A
>> pstmt.setString(2, "\u00E9"); // Acute Latin e
>> pstmt.execute();
>>
>> sqlStatementString = "SELECT * FROM jdbc_demo";
>> sqlStatement.execute(sqlStatementString);
>>
>> rs = sqlStatement.executeQuery(sqlStatementString);
>>
>> JPanel panel = new JPanel();
>>
>> while (rs.next())
>> {
>> String dataString = rs.getString("col");
>> System.out.println("col:" + dataString);
>> panel.add(new JLabel(dataString));
>> }
>> rs.close();
>>
>> JFrame frame = new JFrame();
>> frame.getContentPane().add(panel);
>> frame.setSize(200, 200);
>> frame.setVisible(true);
>>
>> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
>> sqlStatement.execute(sqlStatementString);
>>
>> sqlStatement.close();
>> pstmt.close();
>> con.setAutoCommit(true);
>> }
>> catch (SQLException sqle)
>> {
>> System.out.println("SQL Exeception" + sqle);
>> }
>> }
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
>> changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
"Laurent Schweizer" <laurent.schweizer 'at' peoplefone.com> writes:
> Dear all,
>
> Bellow I have attached the code that I use to do the test, as you can see it's very simple.
>
> I just do the test on windows (via Eclipse) and encoding is OK !
> When I run same class on Linux , in command line, the encoding is not OK.
>
> Did I need to set some specific parameters on linux ?
If your tests are based on printing, you are not sure if your
encoding is ok is no places, I guess your insert/update might
also be using fixed string from sourcecode, which isn't much
reliable either. Your data might even be wrong in the DB.
At least in your test, which is based on System.out.println which
will work differently based on the default encoding of your
system, pipe to hexdump -C and check 0xc3 0xa9 is returned where
the é character should be. It would indicate everything works
well.
> Regards
>
> Laurent
>
>
>
> import java.io.UnsupportedEncodingException;
> import java.nio.ByteBuffer;
> import java.nio.CharBuffer;
> import java.nio.charset.Charset;
> import java.sql.*;
> public class Testing {
>
> Connection conn;
> /**
> * @param args
> */
> public static void main(String[] args) {
> // TODO Auto-generated method stub
> new Testing();
>
> }
>
>
>
> public Testing ()
> {
> try
> {
>
> Class.forName("org.postgresql.Driver").newInstance();
> String url = "jdbc:postgresql://95.128.80.38/testchar?useUnicode=true&characterEncoding=utf8";
> conn = DriverManager.getConnection(url, "postgres", "");
> doTests();
> conn.close();
> }
> catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
> catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
> catch (InstantiationException ex) {System.err.println(ex.getMessage());}
> catch (SQLException ex) {System.err.println(ex.getMessage());}
> }
>
> private void doTests()
> {
> doSelectTest();
>
>
> }
>
> private void doSelectTest()
> {
>
> // String query1 = "SET client_encoding = 'LATIN9';";
>
>
> String query = "SELECT input FROM test ";
> try
> {
>
> // Statement st2 = conn.createStatement();
> // st2.execute(query1);
> Statement st = conn.createStatement();
> ResultSet rs = st.executeQuery(query);
> while (rs.next())
> {
> String s = rs.getString(1);
> System.out.println("val:"+s );
>
> }
> }
> catch (SQLException ex)
> {
> System.err.println(ex.getMessage());
> }
>
>
> /*
> String queryup = "UPDATE test set input ='snom 320 é' WHERE id=1";
> try
> {
> Statement st = conn.createStatement();
> st.execute(queryup);
>
> }
> catch (SQLException ex)
> {
> System.err.println(ex.getMessage());
> }*/
>
> }
>
> -----Message d'origine-----
> De : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp
> Envoyé : samedi 9 février 2013 17:55
> À : Laurent Schweizer; pgsql-jdbc@postgresql.org
> Objet : Re: [JDBC] é converted in é
>
> Hello Laurent,
>
> Use the Method provided to compare the two inserts methods, the one I have demostrated and your process. Reply back
withthe modified method, code, so that we may have a way of duplicating the error you are describing.
>
> I modified the Method this morning and used a update on the extended ASCII chararcter and was still able to to a
system.outand placement in JLabel to observe the correct results.
>
> If you drive to a garage and ask the mechanic to fix your lights on your car because they do not work and he/she
turnsthem on and they work, how do you expect the mechanic to help?
>
> Provide sample code demostrating your issue and perhaps help can be more forth comming.
>
> danap.
>
> Laurent Schweizer wrote:
>> Hello,
>>
>> I see that you directly convert special character , the problem is
>> that with my application data are inserted with another process .
>>
>> When they insert the data in the DB all is ok. I have only an issue
>> with JDBC to get them correctly.
>>
>> Laurent
>>
>>
>>
>>
>> -----Message d'origine-----
>> De : pgsql-jdbc-owner@postgresql.org
>> [mailto:pgsql-jdbc-owner@postgresql.org] De la part de dmp Envoyé :
>> samedi 9 février 2013 02:42 À : laurent.schweizer@peoplefone.com;
>> pgsql-jdbc@postgresql.org Objet : Re: [JDBC] é converted in é
>>
>> > Laurent Schweizer<laurent(dot)schweizer(at)peoplefone(dot)com> wrote:
>> >
>> > > I have an issue with special character like é.
>> >
>> > > I have as server postgres 9.2, I have created a new DB ,
>> encoding> >
>> utf8> > > > Client is a very simple test class that:
>> > > 1) update a varchar value
>> > > 2) read the same value and print them
>> > >
>> > > If I update the varchar with a special character like “é”
>> the> > value in the DB is correct ( I check them with another
>> software )> > but when I read them from my simple java class the value is not> > correct
>> and the é is converted in é> > > > I have added to the connection string
>> the option:
>> > > ?useUnicode=true&characterEncoding=utf8
>> > >
>> > > And if I do a : "SHOW client_encoding;” I get UTF8> > It is
>> behaving as though the client is using a character encoding> other
>> than
>> UTF8 -- some sort of 8-bit encoding, probably. You must> set
>> client_encoding to match.
>> >
>> > -Kevin
>>
>> Hello Laruent,
>>
>> I have tested the following method with the URL parameters you
>> indicated with PostgreSQL 9.0.1 and the latest driver. Both on a linux
>> and windows systems with the same result of the A and the Acute Latin
>> e properly displaying in a system.out and the frame. I suppose it
>> could be modified slightly to also check and update rather than an insert.
>>
>> danap.
>>
>> private void testInsertUTF(Connection con)
>> {
>> // Method Instances
>> String sqlStatementString;
>> Statement sqlStatement;
>> PreparedStatement pstmt;
>> ResultSet rs;
>>
>> try
>> {
>> sqlStatement = con.createStatement();
>> con.setAutoCommit(false);
>>
>> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
>> sqlStatement.execute(sqlStatementString);
>>
>> sqlStatementString = "Create Table jdbc_demo (col VARCHAR(30))";
>> sqlStatement.execute(sqlStatementString);
>>
>> pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES
>> (?), (?)");
>> pstmt.setString(1, "\u0041"); // A
>> pstmt.setString(2, "\u00E9"); // Acute Latin e
>> pstmt.execute();
>>
>> sqlStatementString = "SELECT * FROM jdbc_demo";
>> sqlStatement.execute(sqlStatementString);
>>
>> rs = sqlStatement.executeQuery(sqlStatementString);
>>
>> JPanel panel = new JPanel();
>>
>> while (rs.next())
>> {
>> String dataString = rs.getString("col");
>> System.out.println("col:" + dataString);
>> panel.add(new JLabel(dataString));
>> }
>> rs.close();
>>
>> JFrame frame = new JFrame();
>> frame.getContentPane().add(panel);
>> frame.setSize(200, 200);
>> frame.setVisible(true);
>>
>> sqlStatementString = "DROP TABLE IF EXISTS jdbc_demo";
>> sqlStatement.execute(sqlStatementString);
>>
>> sqlStatement.close();
>> pstmt.close();
>> con.setAutoCommit(true);
>> }
>> catch (SQLException sqle)
>> {
>> System.out.println("SQL Exeception" + sqle);
>> }
>> }
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
>> changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
--
Guillaume Cottenceau
Hello Dave,
sorry for late response, I was on travel.
The ant test seems to work for me:
[javac] /home/are/Downloads/pgjdbc/build.xml:399: warning:
'includeantruntime' was not set, defaulting to build.sysclasspath=last;
set to false for repeatable builds
runtest:
[junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite
[junit] Tests run: 298, Failures: 0, Errors: 0, Time elapsed: 25.658
sec
[junit]
[junit] ------------- Standard Error -----------------
[junit] DatabaseEncodingTest: Skipping UTF8 database tests as test
database encoding is SQL_ASCII
[junit] ------------- ---------------- ---------------
[junit] Testsuite:
org.postgresql.test.jdbc2.optional.OptionalTestSuite
[junit] Tests run: 40, Failures: 0, Errors: 0, Time elapsed: 2.02
sec
[junit]
[junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite
[junit] Tests run: 69, Failures: 0, Errors: 0, Time elapsed: 2.102
sec
[junit]
[junit] Testsuite: org.postgresql.test.xa.XATestSuite
[junit] Tests run: 0, Failures: 0, Errors: 0, Time elapsed: 0.109
sec
[junit]
[junit] ------------- Standard Output ---------------
[junit] Skipping XA tests because max_prepared_transactions = 0.
[junit] ------------- ---------------- ---------------
[junit] Testsuite: org.postgresql.test.extensions.ExtensionsSuite
[junit] Tests run: 0, Failures: 0, Errors: 0, Time elapsed: 0.104
sec
[junit]
[junit] Testsuite: org.postgresql.test.jdbc4.Jdbc4TestSuite
[junit] Tests run: 29, Failures: 3, Errors: 2, Time elapsed: 0.816
sec
[junit]
[junit] Testcase: testSAXRead(org.postgresql.test.jdbc4.XmlTest):
FAILED
[junit] expected:<[B1B2]> but was:<[]>
[junit] junit.framework.ComparisonFailure: expected:<[B1B2]> but
was:<[]>
[junit] at
org.postgresql.test.jdbc4.XmlTest.transform(XmlTest.java:119)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testRead(XmlTest.java:135)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testSAXRead(XmlTest.java:147)
[junit]
[junit]
[junit] Testcase: testStAXRead(org.postgresql.test.jdbc4.XmlTest):
Caused an ERROR
[junit] Not supported: javax.xml.transform.stax.StAXSource@742a06be
[junit] org.apache.xml.dtm.DTMException: Not supported:
javax.xml.transform.stax.StAXSource@742a06be
[junit] at
org.apache.xml.dtm.ref.DTMManagerDefault.getDTM(DTMManagerDefault.java:477)
[junit] at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:699)
[junit] at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1273)
[junit] at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1251)
[junit] at
org.postgresql.test.jdbc4.XmlTest.transform(XmlTest.java:118)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testRead(XmlTest.java:129)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testStAXRead(XmlTest.java:152)
[junit]
[junit]
[junit] Testcase: testStreamRead(org.postgresql.test.jdbc4.XmlTest):
FAILED
[junit] expected:<[B1B2]> but was:<[]>
[junit] junit.framework.ComparisonFailure: expected:<[B1B2]> but
was:<[]>
[junit] at
org.postgresql.test.jdbc4.XmlTest.transform(XmlTest.java:119)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testRead(XmlTest.java:135)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testStreamRead(XmlTest.java:157)
[junit]
[junit]
[junit] Testcase: testDomWrite(org.postgresql.test.jdbc4.XmlTest):
FAILED
[junit] expected:<<[?xml version="1.0"
standalone="no"?><]a><b>1</b><b>2</b></...> but
was:<<[]a><b>1</b><b>2</b></...>
[junit] junit.framework.ComparisonFailure: expected:<<[?xml
version="1.0" standalone="no"?><]a><b>1</b><b>2</b></...> but
was:<<[]a><b>1</b><b>2</b></...>
[junit] at
org.postgresql.test.jdbc4.XmlTest.testWrite(XmlTest.java:188)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testDomWrite(XmlTest.java:197)
[junit]
[junit]
[junit] Testcase: testStAXWrite(org.postgresql.test.jdbc4.XmlTest):
Caused an ERROR
[junit] Can't transform to a Result of type
javax.xml.transform.stax.StAXResult
[junit] javax.xml.transform.TransformerException: Can't transform to
a Result of type javax.xml.transform.stax.StAXResult
[junit] at
org.apache.xalan.transformer.TransformerIdentityImpl.createResultContentHandler(TransformerIdentityImpl.java:302)
[junit] at
org.apache.xalan.transformer.TransformerIdentityImpl.transform(TransformerIdentityImpl.java:330)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testWrite(XmlTest.java:171)
[junit] at
org.postgresql.test.jdbc4.XmlTest.testStAXWrite(XmlTest.java:202)
[junit]
[junit]
[junit] Test org.postgresql.test.jdbc4.Jdbc4TestSuite FAILED
[junit] Testsuite: org.postgresql.test.ssl.SslTestSuite
[junit] Tests run: 0, Failures: 0, Errors: 0, Time elapsed: 0.135
sec
[junit]
[junit] ------------- Standard Output ---------------
[junit] Skipping ssloff8.
[junit] Skipping sslhostnossl8.
[junit] Skipping ssloff9.
[junit] Skipping sslhostnossl9.
[junit] Skipping sslhostgh8.
[junit] Skipping sslhostgh9.
[junit] Skipping sslhostbh8.
[junit] Skipping sslhostbh9.
[junit] Skipping sslhostsslgh8.
[junit] Skipping sslhostsslgh9.
[junit] Skipping sslhostsslbh8.
[junit] Skipping sslhostsslbh9.
[junit] Skipping sslhostsslcertgh8.
[junit] Skipping sslhostsslcertgh9.
[junit] Skipping sslhostsslcertbh8.
[junit] Skipping sslhostsslcertbh9.
[junit] Skipping sslcertgh8.
[junit] Skipping sslcertgh9.
[junit] Skipping sslcertbh8.
[junit] Skipping sslcertbh9.
[junit] ------------- ---------------- ---------------
There are errors but the standard tests seem to work well. What issue
exactly are you referring to please? Which test fails for you?
Best regards
Andreas
On Fri, 2013-02-08 at 09:13 -0500, Dave Cramer wrote:
>
> Andreas this does not pass the built in tests. run ant test to see
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
> On Tue, Feb 5, 2013 at 11:11 PM, Andreas Reichel
> <andreas@manticore-projects.com> wrote:
> Dave,
>
> my previous post was not correct, but I finally found the
> culprit:
>
> For any reason we use bindString:
>
> bindString(i, connection.getTimestampUtils().toString(cal, d),
> Oid.UNSPECIFIED);
>
> which formats Date into a String, but without information on
> the time:
>
> public synchronized String toString(Calendar cal, Date x) {
> if (cal == null)
> cal = defaultCal;
>
> cal.setTime(x);
> sbuf.setLength(0);
>
> if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY)
> {
> sbuf.append("infinity");
> } else if (x.getTime() ==
> PGStatement.DATE_NEGATIVE_INFINITY) {
> sbuf.append("-infinity");
> } else {
> // the date only but no time
> appendDate(sbuf, cal);
> //
> appendEra(sbuf, cal);
> appendTimeZone(sbuf, cal);
> }
>
> showString("date", cal, x, sbuf.toString());
>
> return sbuf.toString();
> }
>
> When I modified this function into:
>
> public synchronized String toString(Calendar cal, Date x) {
> if (cal == null)
> cal = defaultCal;
>
> cal.setTime(x);
> sbuf.setLength(0);
>
> if (x.getTime() == PGStatement.DATE_POSITIVE_INFINITY)
> {
> sbuf.append("infinity");
> } else if (x.getTime() ==
> PGStatement.DATE_NEGATIVE_INFINITY) {
> sbuf.append("-infinity");
> } else {
> appendDate(sbuf, cal);
>
> // obey the time too as java.util.Date holds the time
> sbuf.append(" ");
> appendTime(sbuf, cal, 0);
> //
> appendEra(sbuf, cal);
> appendTimeZone(sbuf, cal);
> }
>
> showString("date", cal, x, sbuf.toString());
>
> return sbuf.toString();
> }
>
> everything works as expected and also everything else still
> worked well.
> (My program uses a lot of date/time conversions so I have some
> confidence).
>
> Now there are two questions please:
>
> a) would you like to apply this small change because
> java.util.Date
> holds time information so we should obey it
>
> b) why is there all this Date/String conversion instead just
> using
> millis/Long? I expected using setDate() gives better
> performance than
> handing over Strings but now I found that it does exactly the
> same and
> in an unexpected 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
> >
> >
>
>
>
>
>
On Wed, Feb 13, 2013 at 12:37 AM, Andreas Reichel <andreas@manticore-projects.com> wrote:
Dave,
please find a test attached. I guess, I can shed some light on it in the
meantime.
To me it seems, that setDate()/getDate() indeed ignores any time values
(hh:mm:ss.s) but parses the day-part only (yyyy-mm-dd).
At the same time, setTimestamp()/getTimestamp() behaves correctly.
(Note: in Postgres Date is Date only without Time, while Timestamp obeys
the Time, I was not completely aware of this. I thought the only
difference between Date and Timestamp is Milliseconds vs. Nanoseconds.)
And this is the crux of the matter SQL DATE does not have time in it. While java.sql.Date being derived from java.util.Date does.
On the same time java.sql.Date and java.util.Date support time
information and in fact you can handover a java.sql.Date parameter to a
Timestamp field.
This is the part when I get confused: the timestamp field holds time
information, the java.sql.Date parameter holds time information -- still
it is cut off in the middle.
Now I would like to suggest a couple of small changes:
a) allow java.util.Date in setObject() and convert it into
java.sql.Timestamp automatically
b) keep the time info whenever setting/returning java.sql.Date to/from a
Timestamp field
However, for the moment I am fine as I understood now the need for using
java.sql.Timestamp when working with time.
And I think that is the way it will remain. I'm afraid this would break way more than it would fix
Dave, please find a test attached. I guess, I can shed some light on it in the meantime. To me it seems, that setDate()/getDate() indeed ignores any time values (hh:mm:ss.s) but parses the day-part only (yyyy-mm-dd). At the same time, setTimestamp()/getTimestamp() behaves correctly. (Note: in Postgres Date is Date only without Time, while Timestamp obeys the Time, I was not completely aware of this. I thought the only difference between Date and Timestamp is Milliseconds vs. Nanoseconds.) On the same time java.sql.Date and java.util.Date support time information and in fact you can handover a java.sql.Date parameter to a Timestamp field. This is the part when I get confused: the timestamp field holds time information, the java.sql.Date parameter holds time information -- still it is cut off in the middle. Now I would like to suggest a couple of small changes: a) allow java.util.Date in setObject() and convert it into java.sql.Timestamp automatically b) keep the time info whenever setting/returning java.sql.Date to/from a Timestamp field However, for the moment I am fine as I understood now the need for using java.sql.Timestamp when working with time. Thank you a lot and best regards Andreas PS:
Attachment
Andreas Reichel, 13.02.2013 06:37: > On the same time java.sql.Date and java.util.Date support time > information and in fact you can handover a java.sql.Date parameter to a > Timestamp field. But due to the removing of the time part this will *not* work. > This is the part when I get confused: the timestamp field holds time > information, the java.sql.Date parameter holds time information -- still > it is cut off in the middle. Yes, that's per documentation. http://docs.oracle.com/javase/6/docs/api/java/sql/Date.html "To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero [...]" Note the part about "setting ... to zero" So as soon as you create a java.sql.Date you lose the time information. If you want to be JDBC compliant there are only two simple things to remember: * use java.sql.Date together with setDate()/getDate() for date columns * use java.sql.Timestamp together with setTimestamp()/getTimestamp() for timestamp columns This works across all DBMS that I have worked with (Oracle is a bit special but in general you should simply treat their DATE as timestamp) The only problem are columns with timezones as none of the Java classes can deal with that. Thomas
I agree that more things would break then fix. setDate() should
not be used to fill a PostgreSQL TIMESTAMP data type field.
It is fine to use in the application java.util.Date, but then just
convert it to the TIMESTAMP type for PostgreSQL in setTimeStamp() by
using java.util.Date.getTime(). Insure the format is correct though
with a SimpleDateFormat.
Timestamp With Time Zone, timestamptz, can also be handle exactly the
same way by just using the formatter to include a time zone.
SimpleDateFormat timeStampFormat;
java.sql.Timestamp dateTimeValue;
java.util.Date dateParse;
timeStampFormat = new SimpleDateFormat("yyyy-MM-DD HH:mm:ss z");
dateParse = timeStampFormat.parse("2008-12-30 15:16:08 MST");
dateTimeValue = new java.sql.Timestamp(dateParse.getTime());
prepared_sqlStatement.setTimestamp(i++, dateTimeValue);
danap
Dave Cramer wrote:
> And I think that is the way it will remain. I'm afraid this would break
> way more than it would fix
>
>
>
> Dave Cramer