Can HQL substract or sum date fields?

Question

A have two entities. For example timing settings and orders.

@Entity
public class TimingSettings{

    @Basic
    private Long orderTimeout = 18000; //for example 18000 sec
    ....
}


@Entity
public class Order{

    @Basic
    @OneToOne
    private TimingSettings timingSettings;

    @Temporal(value = TemporalType.TIMESTAMP)
    @Column(nullable = false)
    protected Date time;
    ....
}

I can't select timeout before and calculate needed order time, because I don't known what orders have what timing settings.

Haw can I perform HQL same as the following:

select o from order o 
left join o.timingSettings ts
where o.time < current_timestamp()+ ts.orderTimeout

Answer 1

Problem is with arithmetics and timestamp, check this thread for some ideas.

Answer 2

This depends on whether you need this to work across all databases or not.

In the latter case (specific database) check your dialect for available date functions. MySQL dialect, for example, supports timediff() and time_to_sec() functions, which would allow you to write your query as:

select o from order o 
  left join o.timingSettings ts
 where time_to_sec(timediff(o.time, current_timestamp())) < ts.orderTimeout

If you need to do this for all databases (or if your database doesn't support needed date functions), Hibernate supports year(), month(), ... second() functions across all dialects. Using these will make your query rather verbose :-) plus you'll have issues with DST.