I have an Oracle database with a DATE column. A sample value from this column is 15/12/2014 15:20:15 in the format DD/MM/YYYY HH:MM:SS.
I have this column defined as follows in my java Entity class:
@Column(name = "SVC_EVENT_END_DTTM", columnDefinition = "timestamp")
private Date dateTime;
The following HQL query works:
public List<ServiceEventLogEntity> getServiceEventLogEntries(DateTime from,
DateTime to) {
Query query = getSession().createQuery("FROM ServiceEventLogEntity e "
+ "WHERE e.dateTime between :start and :end "
+ "ORDER BY e.dateTime");
query.setDate("start", from.toDate());
query.setDate("end", to.toDate());
query.setMaxResults(100);
if(to.isBefore(new DateTime())) {
// Only caching results for queries in the past.
// Otherwise we could cache incomplete results when
// the end of the query range is in the future
query.setCacheable(true);
}
try {
return (List<ServiceEventLogEntity>) query.list();
} catch (HibernateException hex) {
hex.printStackTrace();
}
return null;
}
However, this only seems to work when the start and end times span a day transition, see the trace messages below:
[http-8080-1] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [DATE] - Sat Dec 13 23:35:00 GMT 2014
[http-8080-1] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [DATE] - Sun Dec 14 00:35:00 GMT 2014
I'm guessing that this is due to the fact that my DATE objects only represent a day and don't have a time element. I want to be able to query on minutes/hours not days. I've tried various approaches such as using setTimestamp instead of setDate but this just ends up resulting in the query never coming back with results, even in a much narrower window of time (5 minutes).
How can I modify either my entity or my query so I can go to a more granular level to enable me to query by minute or hour instead of day?
Try to use:
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "SVC_EVENT_END_DTTM")
private Date dateTime;
Some javadoc from Temporal source code says:
[...] This annotation must be specified for persistent fields or properties of type java.util.Date and java.util.Calendar [...]
If you don't need milliseconds, than you can use Oracle's DATE type in column (which is up to seconds). When you need millis than you must use Oracle's TIMESTAMP type.
== EDIT ==
Additionaly you should use query.setTimestamp() instead of query.setDate(). I have checked org.hibernate.Query sources and is says:
/**
* Bind the date (time is truncated) of a given Date object
* to a named query parameter.
*
* @param name The name of the parameter
* @param date The date object
*/
public Query setDate(String name, Date date);
but
/**
* Bind the date and the time of a given Date object to a named query parameter.
*
* @param name The name of the parameter
* @param date The date object
*/
public Query setTimestamp(String name, Date date);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments