openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Evan Ireland" <eirel...@sybase.com>
Subject RE: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Date Sun, 29 Jun 2008 22:37:04 GMT
Dinkar,

I mean (b), except for Sybase/SQLServer, you need to round to two decimal
places (hundredths of a second) not three (milliseconds).

This is what we do in the Sybase Application Server for EJB CMP.

> -----Original Message-----
> From: Dinkar Rao [mailto:dinkar.d91411118@gmail.com]
> Sent: Saturday, 28 June 2008 11:44 a.m.
> To: dev@openjpa.apache.org
> Subject: Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost
> for Informix IDS and SQLServer
> 
> 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