Suitable way of using JPQL BETWEEN clause with dates

Question

Is there any good way to trunc date in JPQL? For instance, I have table with a column in which I write current date (new Date()) and then I need to search through this column using the BETWEEN operator. The problem here is that I need to config date params to beginning and the ending of the day, such as: 2016-05-02 00:00:00 and 2016-05-02-23:59:59. I do have utility methods to do this, but I was wondering if is there any other good alternative.

Answer 1

Unfortunately, DATE_TRUNC is not a function defined in JPQL and as a result, none of the JPA implementations - such as Hibernate - support this function.

DATE_TRUNC can however be registered as a custom function in various JPA implementations.

For example, for Hibernate:

public class DateTruncMetadataBuilderInitializer
        implements MetadataBuilderInitializer {
    @Override
    public void contribute(MetadataBuilder metadataBuilder,
            StandardServiceRegistry serviceRegistry) {
        metadataBuilder.applySqlFunction("DATE_TRUNC",
            new SQLFunctionTemplate(DateType.INSTANCE,
                "DATE_TRUNC(?1, ?2)"));
    }
}

You would then need to register this class in a JAR resource file called META-INF/services/org.hibernate.boot.spi.MetadataBuilderInitializer.

After this change you can use DATE_TRUNC in your JPQL/HQL queries, like so:

SELECT a FROM Entity a 
    WHERE a.dateField BETWEEN DATE_TRUNC('day', :start) AND DATE_TRUNC('day', :end)