Performing Date/Time Math In HQL?

hibernate datetime hql
jdmichal picture jdmichal · Mar 12, 2009 · Viewed 40.2k times · Source

I'm looking how to perform date/time math within an HQL query. Specifically, how do I add or subtract (x) amount of time from the result of the current_timestamp() function? Or do I have to drop into SQL for this and hope that whatever database is being run supports it?

HQL query example:

FROM RandomThing
WHERE randomTime IS NOT NULL AND
      randomTime >= current_timestamp() AND
      randomTime <= (current_timestamp() + :timeToAdd)

I can define the :timeToSubtract parameter to be any particular unit, though anything bigger than hours would be undesirable, and seconds would be most desirable.

CLARIFICATION: I realize this can be easily done outside of the query. But for philosophical reasons, let's say it's important to use the database server's time, rather than the querying system's time. Practical example: I'm querying an automatic timestamp for all entries made within the last (x) amount of time. Since the timestamp is made by the database system, it is important to also use the database's current time.

Answer

ccclark picture ccclark · Mar 12, 2009

Why do you need to do it in the query? Why not just handle it in the java code.

for example:

From RandomThing
Where randomTime is not null and
      randomTime >= :currentTimestamp and
      randomTime <= :maxTimestamp

And then just set the parameters.

Related questions

How to store date/time and timestamps in UTC time zone with JPA and Hibernate

How can I configure JPA/Hibernate to store a date/time in the database as UTC (GMT) time zone? Consider this annotated JPA entity: public class Event { @Id public int id; @Temporal(TemporalType.TIMESTAMP) public java.util.Date date; } If …

Read More
JPA Storing OffsetDateTime with ZoneOffset

I have the following entity class: @Entity public class Event { private OffsetDateTime startDateTime; // ... } However, persisting and then reading the entity to/from the database with JPA 2.2 results in a loss of information: the ZoneOffset of startDateTime changes to UTC (the …

Read More
What are the possible values of the Hibernate hbm2ddl.auto configuration and what do they do

I really want to know more about the update, export and the values that could be given to hibernate.hbm2ddl.auto I need to know when to use the update and when not? And what is the alternative? These …

Read More