Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

date operations in Hibernate

 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any comments/responses please
 
Leonardo Carreira
Ranch Hand
Posts: 494
Eclipse IDE Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gotcha!!!

The below code perfectly works...


 
Mahesh Kedari
Ranch Hand
Posts: 111
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rahul,
I tried this but i am getting an error for day() function.
Below is an error stack. Could you please help me?

 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Mahesh,

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

 
Mahesh Kedari
Ranch Hand
Posts: 111
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic