Обсуждение: JDBC date problem
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).
Aleksey
--
Aleksey Demakov
avd@gcom.ru
On 16 Oct 1998, 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).
Yep. Prior to JDK 1.1.6, setDate() had a bug, where it stored the date 1
day earlier than it really was. I tested both Linux & Win95/nt jdk's, and
this seemed consistent.
About 1 1/2 months ago, I found that 1.1.6 fixed it, so I removed the
increment from the source.
The V6.4 beta driver effectively only works for 1.1.6 or later JDK for the
setDate() method.
--
Peter T Mount peter@retep.org.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
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?
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).
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
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@retep.org.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
Peter T Mount <peter@retep.org.uk> writes:
>
> > 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.
Indeed, in my case this is much better solution. Many thanks to Herouth.
> The Date problem is going to be with us as long as people are still using
> pre 1.1.6 JDK's.
Not quite. Also as long as people are using post 1.1.6 JDK's with the
6.3.2 driver.
> 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.
As for me it's ok.
--
Aleksey Demakov
avd@gcom.ru
On 19 Oct 1998, Aleksey Demakov wrote:
> Peter T Mount <peter@retep.org.uk> writes:
> >
> > > 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.
>
> Indeed, in my case this is much better solution. Many thanks to Herouth.
>
> > The Date problem is going to be with us as long as people are still using
> > pre 1.1.6 JDK's.
>
> Not quite. Also as long as people are using post 1.1.6 JDK's with the
> 6.3.2 driver.
One thing with the 6.4 driver, is that it's backward compatible with
6.3.2. All of the differences between the two have been either
implementing new features, or bug fixes.
The Date problem is the only incompatible change.
> > 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.
>
> As for me it's ok.
>
> --
> Aleksey Demakov
> avd@gcom.ru
>
--
Peter T Mount peter@retep.org.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