1

Im using Oracle 10g,Hibernate 3,springMVC, jqgrid. I have some Date fields in ORACLE which are mapped as follows

@Temporal(TemporalType.DATE)
@Column(name = "DOC_CREATION_DATE", length = 7,insertable=true,updatable=false)
public Date getDocCreationDate() {
    return this.docCreationDate;
}

In my grid I filter date using jqueryCalendar and everything is fine. now I have a new request from the client which is to show the time of documentCreation and they also want to be able to filter by a time range. for example:

find all records created between 6:am and 7:pm, find all records created at 6:am.

I have tried already formatting the date field with

@Temporal(TemporalType.TIMESTAMP)

and that's not what they want

Is there any other approach to this problem, any good ideas how to implement this are very welcome.

the thing is I need to use the same mapped field to query in one column of the Jqgrid for the regular date(12/01/2012 using jqueryCalendar)and add another column for the time part of that same mapped field once that is done I need to query (hibernate criteria) the time column for a range of time something like this mock:

...
 criteria.add(Restrictions.ge("docCreationDate.mappedFieldTimePart",6AM ));

thank you for all the help

Jesse
  • 29
  • 1
  • 9
  • 1
    That's not what they want? If they want to store the time, that's what they want. Using TemporalType.DATE will only store the date part. It's not possible to search for times without storing it. – JB Nizet Jan 31 '12 at 23:12
  • @Jesse check this also http://stackoverflow.com/a/4013601/706695 and here are the parameters that you can use; http://www.techonthenet.com/oracle/functions/to_date.php. But as JB Nizet said you have no chance to filter by time if you dont store it. – HRgiger Jan 31 '12 at 23:50
  • when I was formatting the date and change the mapping to **TIMESTAMP, ** I was able to retrieve the entire time as well, so the time is no issue here, its there I just need to extract that and use it to make the query by CreationTime thankYou all – Jesse Jan 31 '12 at 23:53
  • @JBNizet HRgiger thanks guys you were right about the TemporalType.Date thing. The only reason I was able to get the time was because there was a auditing trigger with timestamp for that particular field all others have 00:00:00 :O – Jesse Feb 01 '12 at 16:58

2 Answers2

3

The column is of type mapped to a Time, and thus you must compare its value with a Time:

Time sixAM = Time.valueOf("06:00:00");
criteria.add(Restrictions.ge("docCreationDate.mappedFieldTimePart", sixAM));

You can also use a regular date:

Date sixAM = new SimpleDateFormat("HH:mm").parse("06:00");
criteria.add(Restrictions.ge("docCreationDate.mappedFieldTimePart", sixAM));
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • thank you I'm working on a different project now but this was definitely part of the solution – Jesse Feb 19 '12 at 04:38
1

You must change the @Temporal annotation to use either TemporalType.TIMESTAMP or add another field and annotate it with TemporalType.TIME. Hibernate uses the @Temporal annotation to determine if the field is to be treated like a java.sql.Timestamp or a java.util.Date with the time lopped-off (set to midnight, or 00h 00m 00.0s). This allows developers to use java.util.Date everywhere in their application and never have to worry about the Timestamp class (it's banished from most of our codebase).

Matt Brock
  • 5,337
  • 1
  • 27
  • 26
  • Thanks Matt that's what I'm doing right now create extra virtual fields annotated with TemporalType.TIME , now the issue is how to make the criteria Query I have never filtered by TIME any suggestions – Jesse Feb 01 '12 at 16:49
  • Not sure how I'd do this with Criteria, but with HQL it would be very simple, as HQL has hour() minute() second() functions available. – Matt Brock Feb 03 '12 at 17:03
  • thank you I'm working on a different project now but this was definitely part of the solution – Jesse Feb 19 '12 at 04:30