Hello,
As a company we've just been adopting PostgreSQL, and we noticed this oddity with the PostgreSQL JDBC driver, not sure if it classifies as a bug, but it caught us out recently, so am just sharing in case it catches anyone else out.
Essentially if reusing a PreparedStatement and clearing the parameters between executes, if you use setObject() with a null object on a TIMESTAMP field and specify Types.DATE then all subsequent updates using setTimestamp() will miss out the time component and just add in the date with time set to midnight.
Below is a snippet (this is just handwritten to demonstrate so apologies if I've made any typos and not demonstrating error checking etc). Of course this was easy to workaround, as we should've been using setObject passing in Types.TIMESTAMP and not Types.DATE (or just using setNull) but I wouldn't have expected the below behaviour!!
I thought I would share with you anyway to see whether you just classify this as "misuse" or whether it is a genuine oddity that may need to be addressed. (versions of PostgreSQL mentioned in comments below). Thanks,
"
// assuming a simple table with one timestamp field such as "CREATE TABLE test ( dt TIMESTAMP )"
// prepare a statement on a postgresql connection
PreparedStatement tStmt = tCon.prepareStatement("INSERT INTO test ( dt ) VALUES ( ? )");
// clear parameters, set timestamp to now and execute
tStmt.clearParameters();
tStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
tStmt.executeUpdate();
// clear parameters, set using a null object and execute
tStmt.clearParameters();
tStmt.setObject(1, null, Types.DATE);
tStmt.executeUpdate();
// clear parameters, set timestamp to now and execute
tStmt.clearParameters();
tStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
tStmt.executeUpdate();
// this will output 3 rows assuming current date / time is 12/02/2013 17:08:01
// 1st row = 12/02/2013 17:08:01
// 2nd row = null
// 3rd row = 12/02/2013 00:00:00
// as you can see the 3rd row has had its time wiped out and set to midnight
// tested against various versions, last test against PostgreSQL 9.1.3 on linux 64 bit
// and using JDBC PostgreSQL 9.2devel JDBC4 (build 1000)
"
P.S - Apologies if anyone has mentioned this before, didn't spot anything similar on the brief searches I did!
--
Alastair Burr
Senior Engineer & Project Coordinator, Bluestar Software
Telephone: +44 (0)1256 882695
Web site: www.bluestar-software.co.uk
Email: alastair.burr@bluestar-software.co.uk
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DISCLAIMER: This email message and any attachments is for the sole
use of the intended recipient(s) and may contain confidential and
privileged information. Any unauthorised review, use, disclosure
or distribution is prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of
the original message.
The views expressed in this message may not necessarily reflect the
views of Bluestar Software Ltd.
Bluestar Software Ltd, Registered in England
Company Registration No. 03537860, VAT No. 709 2751 29
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
