如何在JPQL中使用SQL Server的AT TIME ZONE子句？

核心结论 #

AT TIME ZONE是SQL Server的原生SQL语法，JPQL作为跨数据库的通用查询语言，并不支持这类数据库专属的语法特性，所以Hibernate的JPQL解析器会抛出unexpected token: AT错误——不管是否搭配CONVERT函数使用，JPQL都无法识别AT TIME ZONE关键字。

可行解决方案 #

1. 使用原生SQL查询（最直接高效）

绕过JPQL的语法解析，直接编写SQL Server支持的原生查询语句，通过EntityManager.createNativeQuery执行：

SELECT 
    CONVERT(datetime, PR.requestDate, 120) AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time',
    COUNT(*)
FROM PrivilegeRequest PR
WHERE 
    CONVERT(datetime, PR.requestDate, 120) AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time' >= ?1
    AND CONVERT(datetime, PR.requestDate, 120) AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time' < ?2
GROUP BY 
    CONVERT(datetime, PR.requestDate, 120) AT TIME ZONE 'Central European Time' AT TIME ZONE 'India Standard Time'

注意：这里的表名PrivilegeRequest要和数据库实际表名一致，参数绑定逻辑与JPQL一致，通过setParameter方法设置。

2. 自定义HQL函数（适配JPQL场景）

如果想继续使用JPQL，可以扩展Hibernate，将AT TIME ZONE封装为自定义函数：

• 步骤1：配置自定义函数
  在Hibernate配置文件（如hibernate.cfg.xml）中添加：

  <hibernate-configuration>
    <session-factory>
      <!-- 其他原有配置 -->
      <sql-function name="at_time_zone" class="org.hibernate.dialect.function.StandardSQLFunction">
        <param name="returnType">timestamp</param>
      </sql-function>
    </session-factory>
  </hibernate-configuration>
  

• 步骤2：在JPQL中调用自定义函数

  SELECT 
      at_time_zone(at_time_zone(CONVERT(datetime, PR.requestDate, 120), 'Central European Time'), 'India Standard Time'),
      COUNT(*)
  FROM com.grc.pam.model.entity.PrivilegeRequest PR
  WHERE 
      at_time_zone(at_time_zone(CONVERT(datetime, PR.requestDate, 120), 'Central European Time'), 'India Standard Time') >= ?1
      AND at_time_zone(at_time_zone(CONVERT(datetime, PR.requestDate, 120), 'Central European Time'), 'India Standard Time') < ?2
  GROUP BY 
      at_time_zone(at_time_zone(CONVERT(datetime, PR.requestDate, 120), 'Central European Time'), 'India Standard Time')
  

  该方案依赖Hibernate版本，部分旧版本可能需要自定义函数实现类来适配。

3. 应用层时区转换（小数据量场景）

如果查询的数据量不大，可以先查询出符合原始时间范围的记录，再在Java应用层通过ZonedDateTime、ZoneId等API将requestDate从服务器时区转换为客户端时区，最后完成统计。但这种方式会将数据加载到内存，不适合大数据量查询。

内容的提问来源于stack exchange，提问作者Ram