openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dinkar Rao" <dinkar.d91411...@gmail.com>
Subject Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Date Fri, 27 Jun 2008 16:53:31 GMT
The warnings about Sybase, SQLServer, and Infomix are just a reminder
for folks trying to use precise dates in their code. Due to
limitations in these databases with precision for date types,  what
you get back from the database might not be what you expect.

On the OpenJPA side, we ensure with this fix that we don't compound
the problem by truncating milliseconds.

Thanks
Dinkar

On Fri, Jun 27, 2008 at 5:55 AM, Kevin Sutter <kwsutter@gmail.com> wrote:
> Should this topic be opened as a separate Issue (or sub-task)?  Or, should
> this Issue just be re-opened?  I'm not an expert with this timestamp stuff,
> but it seems like we still have an open issue with this resolution.
>
> Kevin
>
> On Thu, Jun 26, 2008 at 4:13 PM, Dinkar Rao <dinkar.d91411118@gmail.com>
> wrote:
>
>> Ditto for SQLServer.
>>
>> On IDS, the fractional precision is specifiable upto only 5 places, as
>> in "udate DATETIME YEAR TO FRACTION(5)".  So the max fractional value
>> that can be stored is 99999.
>>
>> On Thu, Jun 26, 2008 at 1:29 PM, Evan Ireland <eireland@sybase.com> wrote:
>> > Just a note on this for Sybase databases, for which the resolution is 1
>> > 300th of a second. When using O/R mapping with Sybase ASE, it is best to
>> > round the Timestamp value to the nearest 100th of a second when storing,
>> so
>> > that you don't get unexpected comparison failures when reading the value
>> > back again or using a value in a 'where' clause.
>> >
>> >> -----Original Message-----
>> >> From: Catalina Wei (JIRA) [mailto:jira@apache.org]
>> >> Sent: Friday, 27 June 2008 8:24 a.m.
>> >> To: dev@openjpa.apache.org
>> >> Subject: [jira] Closed: (OPENJPA-645) Date millisecond precision lost
>> for
>> >> Informix IDS and SQLServer
>> >>
>> >>
>> >>      [ https://issues.apache.org/jira/browse/OPENJPA-
>> >> 645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
>> >>
>> >> Catalina Wei closed OPENJPA-645.
>> >> --------------------------------
>> >>
>> >>     Resolution: Fixed
>> >>
>> >> fix checked in under r672017
>> >>
>> >> > Date millisecond precision lost for Informix IDS and SQLServer
>> >> > --------------------------------------------------------------
>> >> >
>> >> >                 Key: OPENJPA-645
>> >> >                 URL:
>> https://issues.apache.org/jira/browse/OPENJPA-645
>> >> >             Project: OpenJPA
>> >> >          Issue Type: Bug
>> >> >          Components: jdbc
>> >> >            Reporter: Dinkar Rao
>> >> >            Priority: Minor
>> >> >         Attachments: patch-645.txt
>> >> >
>> >> >
>> >> > An entity has an attribute of type java.util.Date, annotated with
>> >> @Temporal(TemporalType.TIMESTAMP):
>> >> > @Temporal(TemporalType.TIMESTAMP)
>> >> > public Date udate;
>> >> > This gets mapped in Informix to a column of type:
>> >> > udate DATETIME YEAR TO FRACTION (3)
>> >> > and in SQLServer to
>> >> > udate DATETIME
>> >> > When the udate attribute is assigned a value with millisecond
>> precision,
>> >> say "12:34:56:789", OpenJPA chops off the millisecond fractional part
>> when
>> >> it generates the INSERT statement.
>> >> > In DBDictionary, for this type, we come to setDate() with the 'val'
>> >> parameter set to the correct java.util.Date value "12:34:56:789". (The
>> >> millisecond value is stored in the (Gregorian.Date) cdate.millis
>> attribute
>> >> of java.util.Date). setDate() then calls setTimestamp() - the last else
>> -
>> >> with a new instance of java.sql.Timestamp:
>> >> > setTimestamp(stmnt, idx, new Timestamp(val.getTime()), null, col);
>> >> > java.sql.Timestamp is made up of 2 parts - a date part that stores
the
>> >> time upto seconds, and a separate attribute, called nanos, that stores
>> >> everything that is fractional of seconds.
>> >> > So the new Timestamp value that is sent to setTimestamp() has this:
>> >> > (Gregorian.Date) cdate = 12:34:56
>> >> > nanos = 789000000
>> >> > In setTimestamp() there is a check for supportsTimestampNanos. Because
>> >> in the InformixDictionary and SQLServer dictionaries this is set to
>> false,
>> >> the code then zeros out the nanos field:
>> >> > if (supportsTimestampNanos)
>> >> >     val.setNanos(nanos);
>> >> > else
>> >> >     val.setNanos(0);
>> >> > Consequently, all fractional seconds information is lost for these
2
>> >> database types from the INSERT statement for this timestamp value.
>> >> > The nanos field in java.sql.Timestamp does not really mean that only
>> >> nanoseconds are stored there - it means that any fractional value, after
>> >> seconds  will be stored there.This problem happens not only with the
>> Date
>> >> field in the entity, but also with java.util.Calendar and
>> >> java.sql.Timestamp. The solution is to always set the nanoseconds value
>> in
>> >> the (java.sql.Timestamp)val field. The check for supportsTimestampNanos,
>> >> as well as the flag itself, is not needed, because both IDS and
>> SQLServer
>> >> do allow fractional seconds.
>> >> > Will attach a patch ASAP. Albert has reviewed the proposed solution.
>> >>
>> >> --
>> >> This message is automatically generated by JIRA.
>> >> -
>> >> You can reply to this email to add a comment to the issue online.
>> >
>> >
>> >
>>
>

Mime
View raw message