How to use SQL Server AT TIME ZONE clause with JPQL?-CodePudding

I have to convert date fields in where clause of query from one time zone(server time zone) to another (client time zone). I am able to achieve this for MySQL database using CONVERT_TZ function, but for Microsoft SQL Server, AT TIME ZONE clause throwing error in below JPQL.

select (PR.requestDate AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time'),count(*)  FROM com.grc.pam.model.entity.PrivilegeRequest PR  where 1 = 1  and (PR.requestDate AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time') >= ?1  and (PR.requestDate AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time') < ?2  GROUP BY (PR.requestDate AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time')

Error:

18:13:50.006 [http-nio-8080-exec-10] ERROR org.hibernate.hql.internal.ast.ErrorTracker - line 1:24: unexpected token: AT
antlr.NoViableAltException: unexpected token: AT at org.hibernate.hql.internal.antlr.HqlBaseParser.expressionOrVector(HqlBaseParser.java:5226) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]

Does this AT TIME ZONE clause works in JPQL? or is there any other way to achieve this?

Update:

Query with CONVERT funtion:

select (CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 'India Standard Time'),count(*)  FROM com.grc.pam.model.entity.PrivilegeRequest PR  where 1 = 1  and (CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 'India Standard Time') >= ?1  and (CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 'India Standard Time') < ?2  GROUP BY (CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 'India Standard Time')

Error:

14:23:06.303 [http-nio-8080-exec-4] ERROR 
org.hibernate.hql.internal.ast.ErrorTracker - line 1:46: unexpected 
token: AT
14:23:06.319 [http-nio-8080-exec-4] ERROR 
org.hibernate.hql.internal.ast.ErrorTracker - line 1:46: unexpected 
token: AT
antlr.NoViableAltException: unexpected token: AT


Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: 
unexpected token: AT near line 1, column 46 [select 
(CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 'India Standard 
Time'),count(*)  FROM com.grc.pam.model.entity.PrivilegeRequest PR  
where 1 = 1  and (CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 
'India Standard Time') >= ?1  and 
(CONVERT(datetime,PR.requestDate,120) AT TIME ZONE 'India Standard 
Time') < ?2  GROUP BY (CONVERT(datetime,PR.requestDate,120) AT TIME 
ZONE 'India Standard Time')]

CodePudding user response：

After did some research on this, found that we can not call/use this SQL Server "AT TIME ZONE" clause directly in JPQL.

As explained in MetadataBuilderContributor, used MetadataBuilderContributor utility to customize the MetadataBuilder like below:

public class SqlTimeZoneMetadataBuilderContributor implements MetadataBuilderContributor {

@Override
public void contribute(MetadataBuilder metadataBuilder) {
    metadataBuilder.applySqlFunction("CONVERT_TIMEZONE", new SQLFunctionTemplate(StandardBasicTypes.TIMESTAMP,
            "CONVERT(datetime, ?1 AT TIME ZONE ?2 AT TIME ZONE ?3)"));

}

}

Specified this to jpaProperties property of org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean as property.

<prop key="hibernate.metadata_builder_contributor">com.grc.common.SqlTimeZoneMetadataBuilderContributor</prop>

Usage in JPQL:

and (CONVERT_TIMEZONE(PR.requestDate, '" serverDisplayName "', '" clientDisplayName "')) >= ?