Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] JPQL + date expressions

Hi there,


I’ve been trying to perform date arithmetic using JPA, Querydsl, Eclipselink and h2 (test) or postgres (prod). I believe I’ve got the first two settled, and there is a spot in eclipselink that I don’t fully understand.


My problem: I’ve got an expirationdate in a column of the box table, and I’ve got some validity offsets (in days) in a column of the article masterdata table. I want to select the boxes with a criteria based on the expiration date minus the offset, and compare that to some dates (or the expiration date minus some date, and compare that to the offset, that’s the same).


First of all, I tried using the Querydsl dateExpressions together with Ops.DateTimeOps.ADD_DAYS, but those seems to generate invalid jpql (`add_days` is not defined there I believe, at least eclipselink choked on that identifier). I might have used that wrong.


So I turned to the `OPERATOR` of jpql, as eclipselink claims to support a bunch of them. Two of them seems to do the things I need: DateAdd and DateDifference. With a numberTemplate from Querydsl I managed to generate valid jpql, but eclipselink chocked on the operator and claimed they weren’t defined. I found the spot in the DatasourcePlatform (line 446) where the different operators are registered, and indeed, those both seems to be missing from here.


At that point, I can’t figure out why they aren’t there. Is that simply forgotten, or am I missing a bigger part of the picture? As far as I could figure out, those both operators aren’t even used anywhere. That’s where I thought that I’d better ask here about them.


What am I supposed to do to be able to use the `DateAdd` or the `DateDifference` operator ?


At the end I’m relying on the date arithmetic capabilities of SQL and simply putting a minus sign between my both dates (using a numberTemplate as querydsl doesn’t understand that natively), and hoping that an amount of days comes out. But that’s not really part of the standard.


Thanks for reading that far 😊





Back to the top