You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Dinkar Rao (JIRA)" <ji...@apache.org> on 2008/06/26 22:01:44 UTC
[jira] Created: (OPENJPA-645) Date millisecond precision lost for
Informix IDS and SQLServer
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
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.
Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Kevin Sutter <kw...@gmail.com>.
Thanks for the clarification, Dinkar.
On Fri, Jun 27, 2008 at 11:53 AM, Dinkar Rao <di...@gmail.com>
wrote:
> 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 <kw...@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 <di...@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 <ei...@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.
> >> >
> >> >
> >> >
> >>
> >
>
RE: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Evan Ireland <ei...@sybase.com>.
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 <ei...@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 <kw...@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
> <di...@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 <ei...@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.
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >
> >
> >
Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Dinkar Rao <di...@gmail.com>.
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 <ei...@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 <kw...@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 <di...@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 <ei...@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.
>> >> >
>> >> >
>> >> >
>> >>
>> >
>
>
RE: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Evan Ireland <ei...@sybase.com>.
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 <kw...@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 <di...@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 <ei...@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.
> >> >
> >> >
> >> >
> >>
> >
Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Dinkar Rao <di...@gmail.com>.
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 <kw...@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 <di...@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 <ei...@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.
>> >
>> >
>> >
>>
>
Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Kevin Sutter <kw...@gmail.com>.
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 <di...@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 <ei...@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.
> >
> >
> >
>
Re: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Dinkar Rao <di...@gmail.com>.
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 <ei...@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.
>
>
>
RE: [jira] Closed: (OPENJPA-645) Date millisecond precision lost for Informix IDS and SQLServer
Posted by Evan Ireland <ei...@sybase.com>.
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.
[jira] Updated: (OPENJPA-645) Date millisecond precision lost for
Informix IDS and SQLServer
Posted by "Dinkar Rao (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dinkar Rao updated OPENJPA-645:
-------------------------------
Attachment: patch-645.txt
> 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.
[jira] Updated: (OPENJPA-645) Date millisecond precision lost for
Informix IDS and SQLServer
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-645:
---------------------------------
Affects Version/s: 1.0.3
1.1.0
Fix Version/s: 1.2.0
1.3.0
The changes predate 1.2.0 (1.2.x branch created under revision 680507). It's missed the release notes though and is certainly fixed in 2.0.0M1.
> 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
> Affects Versions: 1.0.3, 1.1.0
> Reporter: Dinkar Rao
> Priority: Minor
> Fix For: 1.0.4, 1.2.0, 1.3.0, 2.0.0-M1
>
> Attachments: OPENJPA-645-1.0.x.patch, 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.
[jira] Updated: (OPENJPA-645) Date millisecond precision lost for
Informix IDS and SQLServer
Posted by "B.J. Reed (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
B.J. Reed updated OPENJPA-645:
------------------------------
Attachment: OPENJPA-645-1.0.x.patch
In 1.0.x, SQL Server has already been updated to allow the fractional portion of seconds to be stored. The OPENJPA-645-1.0.x patch does the same for InformixDictionary and removes the flag from DBDictionary.
> 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: OPENJPA-645-1.0.x.patch, 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.
[jira] Updated: (OPENJPA-645) Date millisecond precision lost for
Informix IDS and SQLServer
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-645:
---------------------------------
Fix Version/s: 2.0.0-M1
1.0.4
> 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
> Fix For: 1.0.4, 2.0.0-M1
>
> Attachments: OPENJPA-645-1.0.x.patch, 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.
[jira] Closed: (OPENJPA-645) Date millisecond precision lost for
Informix IDS and SQLServer
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ 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.