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:[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > >> 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 <[EMAIL PROTECTED]> > 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 > <[EMAIL PROTECTED]> > >> > 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 <[EMAIL PROTECTED]> > >> 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:[EMAIL PROTECTED] > >> >> >> 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. > >> >> > > >> >> > > >> >> > > >> >> > >> > > > > >
