Need help with Hibernate Query language - timestamp field

[dhanya kairali]

Hi,

I have a timestamp field in DB. I want to fetch those rows in DB whose timestamp falls between 2 dates.
I have tried this
select fe from ForwardEntry fe
where fe.myTxTs>=:startDate
and fe.myTxTs<=:endDate

If the time stamp falls on 12/12/2005 05:00 am I want that to be displayed when I specify my start and end date as 12/12/2005. This is not working cos the time field is not matching. How can I format the timestamp field in the query so that the date part alone is considered?

Thanks in advance,
Dhanya

[Thomas Bigbee]

I would be more that happy to help, however, please note that I am a Hibernate Newbie myself. The below code works against a MySQL DataBase, the field on_date in the database is really a TimeStamp field, I know I should have named it on_timestamp, however, it works and I'm just too damn lazy to rewrite the code and database, also please be kind when reviewing the code, this may not be production grade, but at least it works.

  
package test;
 
import java.util.*;
 
import org.apache.log4j.Logger;
import org.hibernate.*;
 
import test.TimeTest;
 
public class Test {
 
public static void main(String[] args) {
Date beginDate;
Date endDate;
int beginYear = 2000;
int beginMonth = 1;
int beginDay = 1;
GregorianCalendar begin = new GregorianCalendar(beginYear, beginMonth, beginDay);

beginDate = begin.getTime();


int endYear = 2004;
int endMonth = 1;
int endDay = 2;
GregorianCalendar end = new GregorianCalendar(endYear, endMonth, endDay);
endDate = end.getTime();

doIt(beginDate, endDate);
}
 


private static void doIt(Date begin, Date end) {
Session session = null;
Transaction tx = null;
Logger log = Logger.getLogger("TestClient");
log.info("creating Employee");
try {
//  get the session from the factory
session = HibernateUtil.currentSession();
//  always start a transaction before doing something
// (even reading) from the database
tx = session.beginTransaction();
//  create a new object
System.out.println(begin);
System.out.println(end);
Query query = session.createQuery("from TimeTest where on_date >=:begin and on_date <:end"); 
query.setDate("begin", begin); 
query.setDate("end", end); 
List list = query.list(); 


Iterator iter = list.iterator();

while ( iter.hasNext()) {
TimeTest t = (TimeTest)iter.next();
System.out.println("Id " + t.getId() + " Name "
+ t.getOnDate());
}

//  commit your transaction or nothing is wrote to the db
tx.commit();
//  clean up (close the session)
HibernateUtil.closeSession();
} catch (HibernateException e) {
e.printStackTrace();
//  when an error occured, try to rollback your
// transaction
if (tx != null){
try {
tx.rollback();
} catch (HibernateException e1) {
e1.printStackTrace();
log.warn("rollback not successful");
}
}
if (session != null) {
try {
HibernateUtil.closeSession();
} catch (HibernateException e2) {
e2.printStackTrace();
log.warn("session close not successful");
}
}
}
}
}