HI
I wanted to run native SQL from hibernate (using createSQLQuery method) and this was working fine for a long time but found a strange issue today.
just a single record is different compare to what is returned from the actual database.

results when the query returns from the db directly
truncdate | count
---------------------+-------
2010-10-03 00:00:00 | 7438
2010-10-03 01:00:00 | 8848
2010-10-03 02:00:00 | 8867
2010-10-03 03:00:00 | 9874
2010-10-03 04:00:00 | 10255

results when the query returns from hibernate
third raw allways get truncdate as 2010-10-03 03:00:00 (this is different what is returned in database 2010-10-03 02:00:00) - I'm truncating hourly so results should get for each hour starting from 00, 01, 02, 03, 04 etc -- but I allways get 00, 01, 03, 03, 04 .. etc. All the other count values are correct

below is the code
StringBuilder strQuery = new StringBuilder();
strQuery.append("SELECT date_trunc('HOUR', event_date) as truncdate, sum(count_event) as count ");
strQuery.append("FROM aggr.aggr_total_events WHERE event_name in ( 'Play' ) AND ( clientid=:clientid OR ownerid=:clientid ) ");
strQuery.append (" AND event_date between '2010-10-03 00:00:00.0' AND '2010-10-03 23:00:00.0' ");
strQuery.append("GROUP BY truncdate, event_name ORDER BY truncdate ");
SQLQuery query = s.createSQLQuery(strQuery.toString());

query.addScalar("truncdate", Hibernate.TIMESTAMP);
query.addScalar("count", Hibernate.INTEGER);

query.setParameter("clientid", clientId);

// Execute the query
List<Object[]> results = query.list();
hibernate jars included in this project
hibernate3.jar
hibernate-annotations.jar
hibernate-commons-annotations.jar

Hi,

which isolation level are you using when accessing the database over hibernate and accessing the database directly?
The differences clould be explained by REPEATEBLE_READ behaviour.
Another possibility is that in Hibernate you have flushed modifications (updates) to the database
but you don't yet commited them. I you access the database in anther session (for example with a query tool)
using READ_COMMITED isolation level, then there you cannot see the modifications as they are not yet commited,
you will see the old values.

Anyway I suggest you to log the jdbc-statements with p6spy,
so you can see which query exaclty is executed by hiberante towards the database.
You can also check if updates are precceding the actual select statements, then probably a flush was executed.
Finally you can copy and paste the p6spy-logged query into your query tool, so you are sure to compare the right queries.

Many thanks for this
I can confirm that there are no updates to the database as I'm working with historical data for BI (Bussiness Intelegence).
I did use the p6spy and used generated query to run in the database directly and got the results as my previous message.

I did another 3 things
1. Change the hibernate3.jar to latest and observed the same error.
2. Did direct JDBC call and checked the results. They all good. That is i got the results for the truncdate for each hour starting from 00, 01, 02, 03, 04 etc which is right.
3. Change the data type from TIMESTAMP to STRING - That is -- used query.addScalar("truncdate", Hibernate.STRING); instead of query.addScalar("truncdate", Hibernate.TIMESTAMP); -- suprisingly I'm getting the right truncdate (as a String) for each hour starting from 00, 01, 02, 03, 04 etc which is right anyway. But the data type for the event_date in database is "timestamp without time zone". We store UTC time all the time.

Hope this is help and appreciate if I can get a help on this

Hi,
is the server running the JVM containing Hibernate having the correct timezone?
The java.util.Date & co classes will interpret the long values retrieved from the database assuming the timezone is the one your server is configured to use.