Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Full stack trace of any exception that occurs:n/a

Mapping Document (partial)


Name and version of the database you are using:MySQL 4.1

The generated SQL (show_sql=true): n/a

This is more a "how to" question that a specific error I am getting.
I have searched the forums, and the net, read the FAQ's etc, and am not finding an answer that seems to work.

Problem: Find the timespan between 2 dates - in any unit.

I've Tried Looking at the various HQL functions (month(timestamp), hour(timestamp), etc). these functions return that part of the timestamp, but I'm looking for the difference between 2 dates.

I came up with a query in the hql scratchpad that appears to return the number of seconds, but it doesn't work in hql, and quite frankly I don't like that I had to figure out that it was returning seconds.... this could be different between RDMS(?).

So I have an hql query that looks like (mostly):


From the hibernate tools, this is returning me a source and a number - some difference that is calculated I'm not sure how.

when run in the application i get an exception regarding "cannot convert value 2 from 12234.72 to TIMESTAMP", so the tool appears to be doing some translation to present the results.

This looks interesting:


but if there are more than 24 hours difference, this fails as only the hours part of the difference is taken into the average.
I've looked at performing this cruft
to get to a number of minutes, but I keep thinking there has got to be a better way.

the only thing I haven't tried is to map the column with type="timestamp", but even after reading the reference docs I'm not sure what kind of impact that would have, as I am sure that at least for now the DB is returning dates of a precision that is fine for my purposes.

Is there an easier way? is there something simple I missed in the docs or HIA? is there more information/clarification I can provide?

your help is greatly appreciated.

Just a thought:

select pl.source, avg( second(pl.childEntry.entryTime) - second(pl.entryTime))
from ProcessLog pl
where pl.childEntry != null AND
pl.entryType = 'info'
group by pl.source

you'd think that was the answer, but that gives you ONLY the seconds as far as I can tell. so the difference between 10:00:00 and 10:30:01 is only 1 second, not 30*60+1, which is what is desired... but thanks for the response.

It shouldn't - note that I do 2 seconds() - you only do one...

I haven't tested it but I'm surprised. Please check you do avg( second(X) - second(Y)) and not avg(second(X-Y)) which is what you posted.

Hi,

I am wondering if you found an answer to your question: basically return a date difference in some unit....I tried doing a select timecol1-timecol2 and that does return a value that seems to be using a logic where

1sec=1
1min=100
1 hour=10000
etc....

so i might be able to work back the actual difference in seconds, minutes....
The trouble is I have a feeling the value returned might be db specific and i don't want that. Thoughts?

Nicolas