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 23:44:10 GMT
Hi Evan,

Do you mean that

(a) Users do want (must take care) to truncate the date value
appropriately on their own before they store it in the date attribute
of some entity ?

or

(b) OpenJPA should take the value stored in a date field of some
entity (e.g. 12:34:56.123456789) and truncate/round it before it
generates the INSERT statement ? This truncation/rounding will have to
be based on the underlying database, and the column definition in the
target table.

With this approach, the truncated value will be 12:34:56.123 for
Sybase/SQLServer. For Informix, based on how the target column was
defined, this could be 12:34:56.1 or 12:34:56.12,...upto
12:34:56.12345. Other databases don't have this problem.

Should OpenJPA be responsible for this truncation ? Or should it pass
through whatever value the user specified (12:34:56.123456789)  to the
INSERT statement and let the database handle the truncation/rounding ?

The pre-fix code used to zero out all fractional part from the INSERT
statement, even when the db was capable of storing (some) fractional
information.

Please clarify whether you intended (a) or (b).

Thanks
Dinkar

On Fri, Jun 27, 2008 at 2:37 PM, Evan Ireland <eireland@sybase.com> wrote:
> In several cases you do want to truncate, just at a certain resolution
> lower than seconds, e.g. 100th of a second.
>
>> -----Original Message-----
>> From: Dinkar Rao [mailto:dinkar.d91411118@gmail.com]
>> Sent: Saturday, 28 June 2008 4:54 a.m.
>> To: dev@openjpa.apache.org
>> Subject: Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost
>> for Informix IDS and SQLServer
>>
>> 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