• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
  • Rob Spoor
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
  • Carey Brown
Bartenders:

date operations in Hibernate

 
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any comments/responses please
 
Ranch Hand
Posts: 494
Eclipse IDE Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Gotcha!!!

The below code perfectly works...


 
Ranch Hand
Posts: 111
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...

 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic