PSQLException: ERROR: syntax error at or near "$1"-CodePudding

I have started to get the exception since I configured timestamp comparing. I can't see where the problem can be. The query runs completely fine in a separate query console:

@Query(value = "select * from notifications n "  
       "where n.time_stamp < (now() at time zone 'Europe/Moscow' - interval :seconds second) order by n.time_stamp desc limit :limit",
       nativeQuery = true)
List<Notification> getNotificationList(@Param("seconds") Integer seconds,
                                       @Param("limit") Integer limit);

Also have tried doing the same thing like this:

@Query(value = "select * from moneta_notification_resend_info n "  
        "where n.time_stamp < (now() at time zone 'Europe/Moscow' - interval ?1 second) order by n.time_stamp desc limit ?2",
        nativeQuery = true)
List<MonetaNotificationResendInfo> getResendInfoList(Integer seconds,
                                                     Integer limit);

the same error popped up

CodePudding user response：

The interval keyword requires a constant, you can't pass the string after that dynamically.

There are two ways to build an interval dynamically:

make_interval(secs => :seconds)

or multiple a fixed interval with the number of seconds:

interval '1 second' * :seconds

Both assumes that the parameter :seconds is an integer