Select entity using between two offset times

Question

In my entity I have two values openTime and closeTime both of the type OffsetTime, now I want to retrieve the entity where the current time is between the openTime and closeTime, but the problem I now have what if openTime = '23:00:00' and closeTime = '05:00:00', meaning that the entity is open at evening and during night. I have the following query:

SELECT s FROM ShopArticle s LEFT JOIN FETCH s.openTimes t WHERE t.openTime < :time AND t.day = :day`

where :time is of type OffsetTime and :day of type DayOfWeek. I can't change openTime to DateTime or sth else, so how do I do this?

score 0 · Answer 1 · answered Mar 24 '21 at 09:47

Without the over-night problem the important part of the where clause should look like this

WHERE t.openTime <= :time 
AND :time <= t.closeTime

With the over-night issue you'd need something like

WHERE (
   t.openTime <= :time 
   AND :time <= t.closeTime
   AND t.day = :day
)
OR (
    (
        (t.openTime <= :time AND t.day = :day) 
    OR 
        (:time <= t.closeTime AND t.day = :day -1)
    )
    AND t.openTime > t.closeTime
)

You can't do date calculations in JPQL though, so :day -1 won't work.

Since you seem to use Spring Data JPA, you can solve that problem with a SpEL expression. How to do that exactly depends on how you pass :day to the query. If it is actually a date-like value, this question and it's answers might be helpful: How do I do date manipulation in SpEL?

Unfortunately JPQL doesn't really have ways to do date calculations, which would come in handy, because you'd have to do something like this:

Select entity using between two offset times

1 Answers1