Re: Timestamp vs. Java Date/Timestamp - Mailing list pgsql-jdbc

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
>         >
>         >
>
>
>
>
>