| [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
😊 Ben -- |