Spring Boot中JPA LocalDateTime在SQLite的日期范围查询失效排查

问题核心原因 #

SQLite与PostgreSQL对日期类型的处理逻辑存在本质差异，导致查询失效，具体体现在以下几点：

1. SQLite无原生TIMESTAMP类型
   SQLite没有真正的日期/时间类型，你定义的columnDefinition = "TIMESTAMP"最终会被存储为文本字符串；而PostgreSQL有原生TIMESTAMP类型，能正确解析和比较日期时间值，这是跨环境表现不一致的根本原因。

2. 日期存储格式与查询参数格式不匹配
   Hibernate的官方SQLite方言在持久化LocalDateTime时，生成的字符串格式可能和你查询时传入的LocalDateTime参数格式不一致（比如时区处理、格式顺序差异）。再加上JDBC URL中date_class=TEXT参数强制日期以文本存储，进一步加剧了格式不兼容问题，导致字符串比较逻辑失效。

3. 字符串字典序 vs 日期逻辑序
   SQLite中对文本类型的日期使用BETWEEN比较时，会按字符串字典序而非日期逻辑顺序排序。如果存储的日期格式不是标准ISO8601（yyyy-MM-ddTHH:mm:ss），字典序和实际日期顺序会出现偏差，符合条件的记录会被错误过滤。

解决办法 #

方案1：统一日期存储为ISO8601格式 #

修改实体类字段配置，强制Hibernate以标准ISO8601格式存储日期字符串，同时调整JDBC配置：

data class Subscriber(
    @Id @GeneratedValue(strategy = GenerationType.UUID)
    val id: String? = null,
    @Column(name = "subscribed_date", columnDefinition = "TEXT")
    @JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss")
    @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
    val subscribedDate: LocalDateTime,
)

更新数据源配置，移除date_class=TEXT参数：

spring.datasource:
    url: jdbc:sqlite:data/db.db
    driver-class-name: org.sqlite.JDBC

方案2：使用原生SQL显式转换日期 #

在Repository中改用原生SQL，通过SQLite内置的datetime()函数将文本日期转换为可比较的日期类型：

interface SubscriberRepository : JpaRepository<Subscriber, String> {
    @Query(value = "SELECT COUNT(s) FROM subscriber s WHERE s.campaign_account_id = :accountId AND datetime(s.subscribed_date) BETWEEN datetime(:startDate) AND datetime(:endDate)", nativeQuery = true)
    fun countNewSubscribersByAccountBetweenDate(
        @Param("accountId") accountId: String, 
        @Param("startDate") startDate: LocalDateTime, 
        @Param("endDate") endDate: LocalDateTime
    ): Long
}

注意：原生SQL无法直接关联实体对象，需要将account参数替换为对应的外键字段（比如campaign_account_id）。

方案3：更换更完善的SQLite方言 #

使用第三方维护的SQLite方言（如com.github.gwenn.sqlite-dialect.SQLiteDialect），它对日期类型的处理更贴近标准JPA行为，需先添加对应Maven/Gradle依赖，再修改JPA配置：

spring.jpa:
    properties:
        hibernate:
            dialect: com.github.gwenn.sqlite-dialect.SQLiteDialect
    database-platform: com.github.gwenn.sqlite-dialect.SQLiteDialect

内容的提问来源于stack exchange，提问作者Hugo Lassiège