Re: Timestamp vs. Java Date/Timestamp

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