Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] JDBC date problem

From: Peter T Mount <peter(at)retep(dot)org(dot)uk>
To: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
Cc: Aleksey Demakov <avd(at)gcom(dot)ru>, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] JDBC date problem
Date: 1998-10-19 05:44:13
Message-ID: Pine.LNX.3.96.981019063722.31130c-100000@maidast.retep.org.uk (view raw or flat)
Thread:
Lists: pgsql-interfaces
On Sun, 18 Oct 1998, Herouth Maoz wrote:

> At 8:20 +0200 on 16/10/98, Aleksey Demakov wrote:
> 
> 
> >
> > Can you believe me?
> >
> > I have a table with a column which is to store the dates each row was
> > created. I fill it with the line:
> >
> >     insert_stmt.setDate(2, new Date (System.currentTimeMillis ()));
> >
> > And every day I insert a new row I can see that I did it tomorrow!
> >
> > I have found that in the method PreparedStatement.setDate() the date
> > argument is intentionally incremented by one. The comments there say
> > that it's to compensate broken SimpleDateFormat. Apparently, my
> > SimpleDateFormat is ok (JDK 1.1.7 for win95/nt and jdk1.1.5 for freebsd).
> 
> For some, it works like that, and for some it doesn't. I'll leave it to the
> author of the JDBC driver to explain why this is done. The question I ask
> is: why do you use the Java current date instead of doing this within the
> INSERT itself?

The bug is with pre 1.1.6 JDK's, and occurs when you set a Date. For some
reason, it set's it 1 day behind the real value. Sun fixed this bug with
the 1.1.6 release.

> That is, if the column to contain the row creation date is of type
> DATETIME, just use now() instead of a ? and a setDate.
> 
> INSERT INTO my_table
>   (creation, other_field1, other_field2, other_field3)
> VALUES
>   ('now', ?, ?, ?);
> 
> Personally, I do this by defining the creation column as a NOT NULL and
> giving it a default (There's a bit of a trick here, because you have to use
> a function, or 'now' will be interpreted as the time of the creation of the
> table, so I define an SQL function which returns 'now'::DATETIME). This
> enables me to use a statement like
> 
> INSERT INTO my_table
>   (other_field1, other_field2, other_field3)
> VALUES
>   (?,?,?);
> 
> And not bother myself about the creation dates (which are automatic).

This is a valid point, and for his case, I'd agree with you.

The Date problem is going to be with us as long as people are still using
pre 1.1.6 JDK's.

I did think of making the driver sence if the bug is present, and then to
account for it, but decided against it, on the grounds of performance.

-- 
       Peter T Mount peter(at)retep(dot)org(dot)uk
      Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
 Java PDF Generator: http://www.retep.org.uk/pdf


In response to

Responses

pgsql-interfaces by date

Next:From: Aleksey DemakovDate: 1998-10-19 08:13:05
Subject: Re: [INTERFACES] JDBC date problem
Previous:From: Tom LaneDate: 1998-10-18 23:55:45
Subject: Re: [INTERFACES] New user questions re libpq++

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group