MSSQL中将13位十进制时间戳转为Java Date的查询问题

问题分析 #

你遇到的报错No converter found capable of converting from type [java.math.BigDecimal] to type [...]，核心原因有三点：

1. 未指定nativeQuery=true，JPA将数据库原生SQL函数（DATEADD、CONVERT）当作JPQL语法解析，导致类型转换逻辑失效
2. 直接对BigDecimal类型的timestmp做除法运算，可能产生非整数结果，导致DATEADD参数类型不匹配
3. 数据库返回的日期类型与Java Date之间未做明确的类型对齐处理

解决方案 #

方案1：修正原生SQL查询（推荐） #

添加nativeQuery=true标记明确使用数据库原生SQL，同时优化时间戳转日期的逻辑，确保类型转换准确：

@Query(value = "select lg.event_id, lg.level_string, lg.formatted_message, lg.caller_filename, lg.timestmp " +
               "from LoggingEvent lg " +
               "where CAST(DATEADD(SECOND, CAST(lg.timestmp AS BIGINT)/1000, '1970-01-01') AS DATE) = CAST(:currentDate AS DATE)",
       nativeQuery = true)
List<LoggingEvent> findByDate(Date currentDate);

• 先将BigDecimal类型的timestmp转为BIGINT，避免除法运算产生的精度问题
• 通过DATEADD将秒级时间戳转为完整日期，再用CAST提取yyyy-MM-dd纯日期部分
• 同时将参数currentDate也转为DATE类型，保证两边类型一致后再做比较

方案2：用JPA属性转换器简化实体映射 #

通过自定义转换器，在实体层自动完成数据库BigDecimal时间戳与Java Date的映射，后续查询无需再手动处理转换：

1. 编写转换器类：

@Converter(autoApply = true)
public class BigDecimalTimestampConverter implements AttributeConverter<BigDecimal, Date> {
    @Override
    public Date convertToDatabaseColumn(BigDecimal timestamp) {
        return timestamp == null ? null : new Date(timestamp.longValue());
    }

    @Override
    public BigDecimal convertToEntityAttribute(Date date) {
        return date == null ? null : new BigDecimal(date.getTime());
    }
}

2. 修改实体类的timestmp字段类型为Date（转换器会自动处理与数据库BigDecimal的双向映射）：

@Column(name = "timestmp")
private Date timestmp;

3. 简化查询语句：

@Query("select lg from LoggingEvent lg where function('DATE', lg.timestmp) = function('DATE', :currentDate)")
List<LoggingEvent> findByDate(Date currentDate);

• function('DATE', ...)用于提取日期的yyyy-MM-dd部分，Hibernate会自动适配不同数据库的日期函数（如MySQL的DATE()、SQL Server的CAST(... AS DATE)）

验证示例 #

输入时间戳1688118257029，通过上述任一方案转换后，会得到日期2023-06-30，可与传入的currentDate正确匹配。

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