Is it possible to have a condition in a JPA @Query statement to determine if a timestamp matches the-CodePudding

I have an existing JPA @Query that I need to add an addition condition to the WHERE such that it'll only return rows where the current date matches that of a timestamp column. Basically wondering if something along the lines of this is possible:

@Query("SELECT .. "  
        "WHERE .. "  
                "AND isCurrentDay(c.completedTs))"

Otherwise can accomplish what I need with a native query.

CodePudding user response：

I'll be it would be safer to do it through native queries using the CURDATE sql statement of the language you are writing in. For example, for MySQL JPA it would look something like this:

@Query("SELECT .. "  
        "WHERE .. "  
                "AND WHERE DATE(`completed_ts`)=CURDATE()", 
       nativeQuery = true)

CodePudding user response：

JPQL Exposes few functions to associate CURRENT date/time :

From the docs :

CURRENT_DATE: This function returns the value of current date on the database server. CURRENT_TIME: This function returns the value of current time on the database server. CURRENT_TIMESTAMP: This function returns the value of current timestamp on the database server.

ref : javadoc

These can be used in the following way to directly access current date

@Query("SELECT .. "  
        "WHERE .. "  
                "AND c.completedTs=CURRENT_DATE )"

Please note , AFAIK , these are vanilla functions and do not support expressions , i.e , you cannot do CURRENT_DATE-1.