Re: Timestamp vs. Java Date/Timestamp

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(dot)xml(dot)transform(dot)stax(dot)StAXSource(at)742a06be
[junit] org.apache.xml.dtm.DTMException: Not supported:
javax(dot)xml(dot)transform(dot)stax(dot)StAXSource(at)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(at)manticore-projects(dot)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(at)manticore-projects(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> >
>
>
>
>
>