Hi,
I have one column last_updated_datetime(holds the last updated date, time values) in petitions table.

I need to find out the number of days between the current date(java.util.Date) and last_updated_datetime column value?

I wrote the code as follows:

Date d = new Date();
List petitionList = session.createQuery("select '" + d + "'-p.lastUpdatedDatetime from Petition p");

In the list I got some -ve number with exponential value. What value it will return and how it will work?

How can I find out the number of days between these two dates?

Thanks in advance.

Hi,
There are atleast a couple of ways to get this value.
i) Send one date from application, pick the other from database.( as done above ).
Concern:
Our application ( Hibernate ) runs on the app server and database on the database server.
Both the machines need not ( and are very unlikely ) be the same.
So, we have to ensure that the dates when compared make sense. ( synchronization, timezones etc...)

Date d = new Date() ;
session.createQuery("select ? - p.lastUpdatedDatetime from Petition p").setParameter(0, d).list() ; // Use Bind variables ; Don't hard code.

ii) Don't send a date from the application. Use sysdate (or some equivalent)
Concern:
Not all databases provide the same function to refer to current date.
So, at times, we might have to change the query.

session.createQuery("select sysdate - p.lastUpdatedDatetime from Petition p").list() ; // Oracle

Return type is Double. ( I guess )
------------------------------------------------
Rate the reply if you find it helpful

Hi,
I am using mysql database(5.0) and have now() for this.

In petition table, last_updated_datetime column is of type datetime.

I updated the code like this:

List l = session.createQuery("select now()-p.lastUpdatedDatetime from Petition p").list();

and getting the following exception message:

WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: S1009
11:15:16,703 ERROR [JDBCExceptionReporter] Cannot convert value '1.1930363E7' from column 1 to TIMESTAMP.
11:15:19,468 INFO [STDOUT] org.hibernate.exception.GenericJDBCException: could not execute query
.
.
.
.
.
Caused by: java.sql.SQLException: Cannot convert value '1.1930363E7' from column 1 to TIMESTAMP.
at com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:5294)
at com.mysql.jdbc.ResultSet.getNativeTimestamp(ResultSet.java:6689)
at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:5314)
at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:2324)
at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:2356)
at org.hibernate.type.TimestampType.get(TimestampType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.hql.QueryLoader.getResultColumnOrRow(QueryLoader.java:352)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)

I am not getting how to fix this problem.

Hi,
>>> I am not getting how to fix this problem.
A couple of options are available.

i) SQL way.
Use session.createSQLQuery() instead of session.createQuery().

ii) HQL way.
Define a propery in Petition mapping.
<property name="age" formula="(now() - mydate)/86400" type="double"/>

------------------------------------------------------
Rate the reply if you find it helpful