Hi,

I am using Hibernate 3.1 with DB2 database. I have a table (AUDIT_ENTRY) that has a column (CHANGE_TS) of type TIMESTAMP. I am trying to fetch rows ordering them by this TIMESTAMP column. The ordering completely ignores the millisecond value of the column. Is this a known behaviour? Is there a way to get around this? Any help on this matter will be highly appreciated.

All details are provided below.

Thanks!

Table structure:

COLUMN - TYPE - SIZE
------------------------
AUDIT_ID - INTEGER - 10
CUST_ID - INTEGER - 10
CHANNEL_TYPE_CD CHAR - 1
CHG_USER_ID - CHAR - 30
CHG_TS - TIMESTAMP - 26
ACTION_CD - CHAR - 10
REASON_TXT - VARCHAR - 100
OBJECT_DSC - VARCHAR - 100
PROPERTY_NM - VARCHAR - 100
OLD_VALUE_TXT - VARCHAR - 100
NEW_VALUE_TXT - VARCHAR - 100

Hibernate Mapping

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.objectedge.audit.framework">
<class name="AuditEntry" table="AUDIT_ENTRY">
<id name="id" column="AUDIT_ID" type="integer">
<generator class="sequence">
<param name="sequence">AUDIT_ENTRY_SEQ</param>
</generator>
</id>
<property name="user" column="CHG_USER_ID" type="string"/>
<property name="customer" column="CUST_ID" type="integer"/>
<property name="channel" column="CHANNEL_TYPE_CD" type="string"/>
<property name="when" column="CHG_TS" type="timestamp"/>
<property name="what" column="OBJECT_DSC" type="string"/>
<property name="action" column="ACTION_CD" type="string"/>
<property name="reason" column="REASON_TXT" type="string"/>
<property name="property" column="PROPERTY_NM" type="string"/>
<property name="oldValue" column="OLD_VALUE_TXT" type="string"/>
<property name="newValue" column="NEW_VALUE_TXT" type="string"/>
</class>
</hibernate-mapping>

Code (including the HQL Query) that fetches data

Session session = sessionFactory.getCurrentSession();
Query query = session.createQuery(
"from com.objectedge.audit.framework.AuditEntry as audit where audit.customer=:id order by audit.when desc");
query.setInteger("id",customerId);
return query.list();

According to the SQL-92 standard (haven't read the newer standards, but I assume that it's the same), timestamp is accurate to 3.33 milliseconds. You cannot order more accurately than that, unless DB2 provides another data type that is more accurate. If you need additional accuracy, change the type to bigint and store the java epoch time (see Date.getTime()).

Hi tenwit,

Thank you very much for the suggestion. I'll try the alternative that you provided and see if it gives better results.