How to use the MySQL CONVERT_TZ function in a JPQL or HQL query

Question

I currently have table in a MySQL database which has a startDate and endDate which are both timestamps. This is linked to the code using hibernate.

I can return these by using the following HQL

SELECT startDate, endDate FROM Reservation where id = :id

What I am trying to do is return these dates based on a given timezone. I know MySQL has a function CONVERT_TZ which can return the dates based on a given timezone, I'm wondering if HQL has a similar function?

I know HQL has a TIMESTAMP_WITH_ZONE function but this uses a local timezone when I need to specify the timezone, can this be done?

Answer 1

Since Hibernate ORM 5.2.18

Since 5.2.18, you can register SQL functions via the MetadataBuilderContributor:

public class SqlFunctionsMetadataBuilderContributor 
        implements MetadataBuilderContributor {
         
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "convert_tz", 
            new StandardSQLFunction( "convert_tz", StandardBasicTypes.TIMESTAMP )
        );
    }
}

Ans simply provide the MetadataBuilderContributor via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Prior to Hibernate ORM 5.2.18

Or, if you bootstrap with the Hibernate native mechanism, you can register the function when constructing the MetadataBuilder as explained in the Hibernate User Guide.

A very common, yet naive way to register a SQL function, is override the MySQL Dialect and register the new function like this:

class CustomMySQLDialect extends MySQL5InnoDBDialect {
    public CustomMySQLDialect() {
        super();
        registerFunction( "convert_tz", new StandardSQLFunction( "convert_tz", StandardBasicTypes.TIMESTAMP ) );
    }

}

The configure Hibernate to use the new dialect:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.CustomMySQLDialect"
</property>