[eclipselink-users] EclipseLink FUNCTION with nested statements

[John Patrick <nhoj.patrick@xxxxxxxxx>]

Hi,

I've been trying to fix this for about a week now with no luck.

This is the MySQL I'm trying to recreate;

SELECT t.*

FROM TEST t
WHERE t.CREATED_AT <= DATE_SUB(NOW(), 'INTERVAL 1 HOUR')
;

I can't trust the client date time, also the client might be in different timezones. So I need to use NOW(), I also need to parametrise the interval and period.

Has anyone else been able to get nested FUNCTION's to work?

I've tried the following JPQL...

SELECT t FROM Test t WHERE t.createdAt <= FUNCTION('DATE_SUB', FUNCTION('NOW'), 'INTERVAL :val :period') - DOESN'T WORK

SELECT t FROM Test t WHERE t.createdAt <= FUNCTION('DATE_SUB', 'NOW()', 'INTERVAL :val :period') - DOESN'T WORK

SELECT t FROM Test t WHERE t.createdAt <= FUNCTION('DATE_SUB', 'NOW', 'INTERVAL :val :period') - DOESN'T WORK

I've done a lot of google queries which all point to using FUNCTION but I can't find anything useful about nested function calls.

Cheers,
John