date operations in Hibernate

[Rahul Babbar]

Hi,

I have a database table A which has among other columns, a column of type date(which is infact, full timestamp)..
I want to query the table so that i get all the records for my current date.

Ex. if the date column has values like "27th April ,10 PM", "28th April 5 AM", "28th April 11 AM", "29th April 12:30 PM",
i want to get the two records having "28th April 5 AM" and "28th April 11 AM".

An hql like "Select a from A a where a.date = :date" and setting "date" as new Date() doesn't return any value(understandably so,because it compares timesstamps)

P.S. the dates representation which i have shown in the above example is just for clarity, they are infact stored as timestamp in the DB.

Thanks a lot

[Rahul Babbar]

Any comments/responses please

[Leonardo Carreira]

Hi Rahul...

i think your HQL should be :

    
"Select a from A a where a.date = CURRENT_DATE()"

CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP()
Returns the date and/or time of the database management system machine

Hope that help..
Correct me if i'm wrong,,

Cheers,

[Rahul Babbar]

Leonardo Carreira wrote:

    
"Select a from A a where a.date = CURRENT_DATE()"

CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP()
Returns the date and/or time of the database management system machine

Thanks,

but i doesnt really work, it makes the sql statement where the suffix where clause is "a.date = current_date" and returns me no records(probably because it compares timestamps). We might need a greater than or equal to clauses for this..

The closest which i could think of is mentioned below, but it doesnt compare "only dates" either...
I preferred to set the value instead of current date because the date may not always be current date, may be current date + 7 days..

  Calendar cal = Calendar.getInstance();
	        cal.setTime(new Date());
			String str = "Select msg from Message msg where msg.dateTime >= :date1 and msg.dateTime <= :date2";
			Query query = session.createQuery(str);
			query.setDate("date1", cal.getTime());
			cal.add(Calendar.DATE, 1);
			query.setDate("date2", cal.getTime());
  

but i was expecting a better date API to use, as the above compares the timestamp, and not just the date....

[Rahul Babbar]

Gotcha!!!

The below code perfectly works...

  
                       Calendar cal = Calendar.getInstance();
	                cal.setTime(new Date());
			String str = "Select msg from Message msg where day(msg.dateTime) = :date and month(msg.dateTime) = :month and year(msg.dateTime) = :year";
			Query query = session.createQuery(str);
			query.setInteger("date", cal.get(Calendar.DATE));
			query.setInteger("month", cal.get(Calendar.MONTH)+1);
			query.setInteger("year", cal.get(Calendar.YEAR));
			
			List<Message> list = query.list();  

[Mahesh Kedari]

Hi Rahul,
I tried this but i am getting an error for day() function.
Below is an error stack. Could you please help me?

 
Exception Description: Syntax error parsing the query [Select a from AppointmentMaster a where day(a.startTime) = :startdate and month(a.startTime) = :startMonth and year(a.startTime) = :startYear], line 1, column 41: unexpected token [day].
Internal Exception: line 1:41: unexpected token: day
        at oracle.toplink.essentials.exceptions.EJBQLException.unexpectedToken(EJBQLException.java:389)
        at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.handleANTLRException(EJBQLParser.java:350)
        at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.addError(EJBQLParser.java:278)
        at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.reportError(EJBQLParser.java:378)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.arithmeticPrimary(EJBQLParser.java:3617)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.arithmeticFactor(EJBQLParser.java:3513)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.arithmeticTerm(EJBQLParser.java:3407)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.simpleArithmeticExpression(EJBQLParser.java:1021)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.arithmeticExpression(EJBQLParser.java:2304)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.simpleConditionalExpression(EJBQLParser.java:2282)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.conditionalPrimary(EJBQLParser.java:2218)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.conditionalFactor(EJBQLParser.java:2155)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.conditionalTerm(EJBQLParser.java:2030)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.conditionalExpression(EJBQLParser.java:1989)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.whereClause(EJBQLParser.java:507)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.selectStatement(EJBQLParser.java:184)
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.document(EJBQLParser.java:135)
        at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.parse(EJBQLParser.java:166)
        at oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.buildParseTree(EJBQLParser.java:127)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:215)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:189)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:153)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:114)
        at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:99)
        at oracle.toplink.essentials.internal.ejb.cmp3.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
        at oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl.createQuery(EntityManagerImpl.java:204)
        ... 13 more
Caused by: line 1:41: unexpected token: day
        at oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.arithmeticPrimary(EJBQLParser.java:3611)
        ... 34 more
   

[Rahul Babbar]

Hi Mahesh,

You are getting the error because you are using Toplink as your persistence provider and not Hibernate....

[Mahesh Kedari]

Thanks Rahul for reply.
Is it possible to implement the same functionality in toplink as wel?
My requirement is exactly same as yours. I am storing the timestamp of records and want to get a records of specific Date.

[Rahul Babbar]

Hi Mahesh,

I guess Toplink may (but need not) provide a syntax for achieving this.

I have not worked on Toplink..so i dont have much idea about it..

You need to see the Toplink documentation to see how easily it provides support for date related operations...

[Paul Sturrock]

Any reason you don't just bind a sql date (rather than a Date object)? Seems to me that's a better idea than introducing DB specific functions.