Re: [JDBC] Prepared statement performance... - Mailing list pgsql-general

Dmitry Tkach wrote:
> Barry Lind wrote:
>
>>
>>
>> Dmitry Tkach wrote:
>>
>>
>>> True... But how does PreparedStatement.setTimestamp () help here?
>>> All it does (at least in 7.2) is Timestamp.toString () :-)
>>>
>>
>> Huh?  In 7.3 setTimestamp() is much more than Timestamp.toString()
>> (and in 7.2 it was as well, I think you need to go back to 7.1 for it
>> to be as simple as you describe).
>
>
> Not really... It *seems* to be doing  more in 7.2, but then here is what
> it says right after it's done:
>
>                        // The above works, but so does the following.
> I'm leaving the above in, but this seems
>                        // to be identical. Pays to read the docs ;-)
>                        //set(parameterIndex,"'"+x.toString()+"'");
>
> And this is exactly right :-)

Those were old comments that were incorrect, thus they are removed in 7.3.

>
> I have indeed seen that timezones did not use to work correctly with 7.2
> JDBC, but it seems that all that needed to be done to fix that was to
> remove the df.setTimezone() call (it shifts the time to GMT, and sends
> it to the server that way, but the backend assumes it is in the local
> timezone, so the timestamp ends up having wrong time), or, even better,
> just get rid of the whole formatting stuff alltogether and stick to the
> toString () call as that comment suggets :-)

That doesn't work when the client and server are running in different
timezones.

> Everything would work, except for that 7.1 thing of course, that
> converts 59.999 to 60.00 :-)
>
> Still all of the above would only work with your local timezone, and, if
> the timestamp has some other offset, it would still be wrong. The
> complexity of the 7.3 code
> seems to be inteded to fix that, which is a good thing, although,  the
> way it is done seems to be way overcomplicated to me - it seems that if
> you just did x.toGMTString () and sent it to the backend as text, it
> would do the same conversion automatically...

This is wrong if the datatype is timestamp with out timezone, but would
do the correct thing (I think) for timestamp with timezone.

> BTW, the same approach could be used if you wanted to use Statement to set
> a timestamptz properly, without calling setTimestamp ():
>
> c.createStatement ().executeQuery ("select * from foo where
> timestamptzfield = '" + mytimestamp.toGMTString () +"');
>
> It's true that not every database would understand GMT format (for
> example, informix doesn't), but,
> on the other hand, not every database has the notion of timezones either
> (informix doesn't either) - i.e., if you are using anything other than
> the locale's default timezone in your application (which would just work
> if you did Timestamp.toString () and sent it in as text), then you are
> already aware that you are working with postgres, and, thus the
> 'database abstraction' argument for using 'set*()' stuff doesn't shoot :-)
>
> and actually, I don't even see any reason why something like:
>
> c.createStatement ().executeQuery ("select * from foo where
> timestamptzfiled = '" + mytimestamp + "'")
>
> ... would not do the same thing as the example above...
>
> To cut it short, the bottomline is - if JDBC implementation just ignored
> the timezone stuff alltogether, and left it all to the backend,
> everything would just work :-)
>
> I may be wrong here, but if I am, I would be very curious to see an
> example where this would not work.
>

See responses above.  If you have suggested improvements try them out
and if you can get them to pass the TimestampTest.java regression I
would like to look at them.

thanks,
--Barry