Re: svn commit: r532412 - in /ofbiz/trunk/applications: order/entitydef/ product/script/org/ofbiz/product/inventory/ product/servicedef/

[David E Jones-2]

On Apr 25, 2007, at 11:20 AM, [hidden email] wrote:

> Author: sichen
> Date: Wed Apr 25 10:20:21 2007
> New Revision: 532412
>
> URL: http://svn.apache.org/viewvc?view=rev&rev=532412
> Log:
> Adding a sequenceId field to the OrderItemShipGrpInvRes entity and  
> ordering secondarily by this field when inventory reservations are  
> retrieved in the balanceInventoryItems service, as an aid to  
> prioritizing reservations when a RDBMS (such as MySQL) doesn't  
> support fractional seconds on timestamp fields

Has anyone looked into doing something with MySQL so that timestamp  
fields are sub-second? I believe the general SQL convention for this  
is actually detailed down to nano-seconds. Is there another data type  
we should be using, or perhaps doing something different with the  
JDBC driver?

-David

[cjhowe]

This is still an open bug for MySQL

http://bugs.mysql.com/bug.php?id=8523


--- "David E. Jones" <[hidden email]> wrote:

>
> On Apr 25, 2007, at 11:20 AM, [hidden email] wrote:
>
> > Author: sichen
> > Date: Wed Apr 25 10:20:21 2007
> > New Revision: 532412
> >
> > URL: http://svn.apache.org/viewvc?view=rev&rev=532412
> > Log:
> > Adding a sequenceId field to the OrderItemShipGrpInvRes entity and
>
> > ordering secondarily by this field when inventory reservations are
>
> > retrieved in the balanceInventoryItems service, as an aid to  
> > prioritizing reservations when a RDBMS (such as MySQL) doesn't  
> > support fractional seconds on timestamp fields
>
> Has anyone looked into doing something with MySQL so that timestamp  
> fields are sub-second? I believe the general SQL convention for this
>
> is actually detailed down to nano-seconds. Is there another data type
>  
> we should be using, or perhaps doing something different with the  
> JDBC driver?
>
> -David
>
>

[jonwimp]

David,

MySQL timestamp resolution is 1 second, I believe.

Also, I think it's not a good practice to index on a timestamp field, and hope that there won't be
duplicates within the smallest unit of time. It may be silly to think that 2 operations, say
"Create New Account (with timestamp as part of primary key)", could collide within a single
nanosecond. Or is it? Will things get easier or worse when you have clustering?

If you still want nanoseconds in the timestamp fields, you'll need to create your own data type
that is a combination of at least 2 MySQL data types.

Jonathon

David E. Jones wrote:

>
> On Apr 25, 2007, at 11:20 AM, [hidden email] wrote:
>
>> Author: sichen
>> Date: Wed Apr 25 10:20:21 2007
>> New Revision: 532412
>>
>> URL: http://svn.apache.org/viewvc?view=rev&rev=532412
>> Log:
>> Adding a sequenceId field to the OrderItemShipGrpInvRes entity and
>> ordering secondarily by this field when inventory reservations are
>> retrieved in the balanceInventoryItems service, as an aid to
>> prioritizing reservations when a RDBMS (such as MySQL) doesn't support
>> fractional seconds on timestamp fields
>
> Has anyone looked into doing something with MySQL so that timestamp
> fields are sub-second? I believe the general SQL convention for this is
> actually detailed down to nano-seconds. Is there another data type we
> should be using, or perhaps doing something different with the JDBC driver?
>
> -David
>