Обсуждение: Timestamp Problems

I've got an application which uses timestamps (without timezones) and
uses Hibernate to abstract away all the raw SQL code. A timestamp
field is mapped to a java.util.Date object. Versions in use:

PostgreSQL 8.3.3
JDBC Driver 8.3-603.jdbc4
Hibernate 3.2.6
Java 1.6.0_07 on Linux

A problem which recently cropped up on one development machine (and I
haven't been able to duplicate the issue off this machine) is that for
some reason the timestamp that gets inserted in to the database gets
converted to UTC. The timezone is explicity set by using the TZ
environment variable to America/New_York, the same issue occurs when
passing the -Duser.timezone=America/New_York parameter to the JVM.

What is strange is that this does not happen all the time, and that it
only affects this one particular column!

I did a search of the archives and found a few similar issues, but
they seemed to occur all the time, not periodically.

Here's a sample of what happens. Timestamp 1/2 are from the same
column/row, but before/after the database update and refresh. I
haven't modified the timestamp. I've looked at the raw SQL, and the
date is indeed getting converted to UTC (4 hours diff in this case).

Timestamp 1 = 2008-07-23 18:33:59.0
Update & Refresh
Timestamp 2 = 2008-07-23 22:33:59.0

What's strange is that if I take the date and create a new Date using
the original's getTime function, it looks like this:

Timestamp 1 = 2008-07-23 18:33:59.0 - new Date: Wed Jul 23 18:33:59 EDT 2008
Update & Refresh
Timestamp 2 = 2008-07-23 22:33:59.0 - new Date: Wed Jul 23 18:33:59 EDT 2008

Yeah, that's right, getTime() is returning the same value even though
toString() prints different values and different values get inserted
in to the database!

Even if I modify the timestamp, the same issue can crop up with the
exact same symptoms.

After a lot of troubleshooting, I've finally figured out that by
disabling the stored procedure cache used by Hibernate (I've
configured Hibernate to use c3p0's database connection pool and
prepared statement cache) this issue doesn't crop up any more.

Anyone see this before? Any idea where the bug lies?

Thanks

-Dave

On Wed, 23 Jul 2008, David Rees wrote:

> What is strange is that this does not happen all the time, and that it
> only affects this one particular column!

Anything going wrong should be reproducible unless your app is doing
something weird temporarily behind the scenes like TimeZone.setDefault.

> Timestamp 1 = 2008-07-23 18:33:59.0 - new Date: Wed Jul 23 18:33:59 EDT 2008
> Update & Refresh
> Timestamp 2 = 2008-07-23 22:33:59.0 - new Date: Wed Jul 23 18:33:59 EDT 2008
>
> Yeah, that's right, getTime() is returning the same value even though
> toString() prints different values and different values get inserted
> in to the database!

You need to check getTimezoneOffset on the two timestamp values.  You
can see the dates being printed with the timezone showing they're the
same, but I'm betting they aren't for the timestampts.

> After a lot of troubleshooting, I've finally figured out that by
> disabling the stored procedure cache used by Hibernate (I've
> configured Hibernate to use c3p0's database connection pool and
> prepared statement cache) this issue doesn't crop up any more.
>

I can't imagine why that would be relevant.

Kris Jurka