[FIXED] Java Spring/PgSQL operation with timestamp returns interval

Issue

I'm trying to retrieve data within an interval of timestamp on Java Spring, using a Postgresql database. One of the queries i'm using, works just fine (It returns data from the last 24 hours):

  @Query(value = "SELECT timestamp,lettura,stato FROM dati_impianti 
WHERE sigla_impianto = :impianto and timestamp > now() - interval '24 hours' 
ORDER BY timestamp DESC", nativeQuery = true)
  List<ReadingEntity> findLastDay(@Param("impianto") String s);

However, when i try to return data with a different timestamp, i get a PSQL exception:

 @Query(value = "SELECT timestamp,lettura,stato FROM dati_impianti 
WHERE sigla_impianto = :impianto and timestamp > :timestamp - interval '24 hours' 
ORDER BY timestamp DESC", nativeQuery = true)
  List<ReadingEntity> findDate(@Param("impianto") String s, @Param("timestamp") Timestamp t);

And this is the exception i'm getting:

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone > interval
No operator matches the given name and argument types. You might need to add explicit type casts.

What i cannot understand is that, according to the PostgreSQL documentation, a timestamp - interval should return another timestamp. However from my query, it seems that i'm comparing a timestamp (from the table) with an interval, which is wrong. If i try to use directly my timestamp parameter (in the form of timestamp > :timestamp without reducing it by an interval), it works but it's not what i was looking for.

I also tried to explicit cast already, by using :timestamp::timestamp but this seems to give another kind of error because of Spring and the way it handles the parameters.

Solution

You might want to try explicitly casting your parameter to a timestamp:

where 
    sigla_impianto = :impianto 
    and timestamp > :timestamp::timestamp - interval '24 hours'

Or, possibly:

where 
    sigla_impianto = :impianto 
    and timestamp > cast(:timestamp as timestamp) - interval '24 hours'

Answered By - GMB
Answer Checked By - Mildred Charles (JavaFixing Admin)