Re: Hibernate, JBoss, PostgreSQL, timestamp read doesn't match during update

Roland Roberts wrote:
> Oliver Jowett wrote:
>> Roland Roberts wrote:
>>
>>> Yes, restrict the timestamp to millisecond precision. Somewhere the
>>> sub-millisecond parts are getting lost. I have no idea if it is in the
>>> JDBC layer or somewhere in Hibernate. Any clues on figuring this out?
>>>
>> Is the server built with integer_datetimes? Maybe you are just hitting a
>> rounding problem.
>>
> Which server; do you mean PostgreSQL or JBoss? I think you mean JBoss,
> but I'm not sure how to check on that. If you know, please share. I'll
> see what I can find out.

No, I mean the PostgreSQL server.

See http://www.postgresql.org/docs/8.3/static/datatype-datetime.html,
specifically this bit:

Note: When timestamp values are stored as double precision
floating-point numbers (currently the default), the effective limit of
precision might be less than 6. timestamp values are stored as seconds
before or after midnight 2000-01-01. Microsecond precision is achieved
for dates within a few years of 2000-01-01, but the precision degrades
for dates further away. When timestamp values are stored as eight-byte
integers (a compile-time option), microsecond precision is available
over the full range of values. However eight-byte integer timestamps
have a more limited range of dates than shown above: from 4713 BC up to
294276 AD. The same compile-time option also determines whether time and
interval values are stored as floating-point or eight-byte integers. In
the floating-point case, large interval values degrade in precision as
the size of the interval increases.

Try "SHOW integer_datetimes;" via psql to see how your server is configured.

-O