aspose file tools*
The moose likes Object Relational Mapping and the fly likes date operations in Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "date operations in Hibernate" Watch "date operations in Hibernate" New topic
Author

date operations in Hibernate

Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
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
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Any comments/responses please
Leonardo Carreira
Ranch Hand

Joined: Apr 07, 2009
Posts: 489

Hi Rahul...

i think your HQL should be :



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,

Sorry, perhaps my english language isn't too good.. Prepare for SCJP 6, Please God help me.. ☼
References : [Java.Boot] [JavaChamp] [JavaPrepare]
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Leonardo Carreira wrote:



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..



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

Joined: Jun 28, 2008
Posts: 210
Gotcha!!!

The below code perfectly works...


Mahesh Kedari
Ranch Hand

Joined: Nov 28, 2009
Posts: 111
Hi Rahul,
I tried this but i am getting an error for day() function.
Below is an error stack. Could you please help me?



Regards,
Mahesh Kedari - Fidus Technologies Ltd.
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Hi Mahesh,

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

Mahesh Kedari
Ranch Hand

Joined: Nov 28, 2009
Posts: 111
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
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: date operations in Hibernate