Spring JPA native query for date-time MySQL and PostgreSQL

Spring JPA allows us to use native queries, but a downside of this approach is that the query is sent directly to database, and we need to make sure that we provide a valid SQL statement, and follow the specific dialect of the database being used. The only time we should be using it is when something can not be achieved via JPQL.

Suppose we want to write a date time native query and want it to run on both MySQL and PostgreSQL we will normally do something like this:

The above query which is technically correct will work only in MySQL but will fail in PostgreSQL. We need to explicitly cast the date time to timestamp for it to work in PostgreSQL like this:

The above query will now work for both MySQL and PostgreSQL

Note that in PostgreSQL we have 2 kinds of timestamp. Simple timestamp for date time without timezone, and timestamp with time zone for date time with timezone so we need to cast accordingly.