I am in a spring boot application and I use JPA
I have an entity (I removed what is useless for this question) :
<code>data class Subscriber(
@Id @GeneratedValue(strategy = GenerationType.UUID)
val id: String? = null,
@Column(name = "subscribed_date", columnDefinition = "TIMESTAMP")
val subscribedDate: LocalDateTime,
)
</code>
<code>data class Subscriber(
@Id @GeneratedValue(strategy = GenerationType.UUID)
val id: String? = null,
@Column(name = "subscribed_date", columnDefinition = "TIMESTAMP")
val subscribedDate: LocalDateTime,
)
</code>
data class Subscriber(
@Id @GeneratedValue(strategy = GenerationType.UUID)
val id: String? = null,
@Column(name = "subscribed_date", columnDefinition = "TIMESTAMP")
val subscribedDate: LocalDateTime,
)
And the repository
<code>interface SubscriberRepository : JpaRepository<Subscriber, String> {
@Query("SELECT COUNT(s) FROM Subscriber s WHERE s.subscribedDate BETWEEN :startDate AND :endDate")
fun countNewSubscribersByAccountBetweenDate(startDate: String, endDate: String): Long
</code>
<code>interface SubscriberRepository : JpaRepository<Subscriber, String> {
@Query("SELECT COUNT(s) FROM Subscriber s WHERE s.subscribedDate BETWEEN :startDate AND :endDate")
fun countNewSubscribersByAccountBetweenDate(startDate: String, endDate: String): Long
</code>
interface SubscriberRepository : JpaRepository<Subscriber, String> {
@Query("SELECT COUNT(s) FROM Subscriber s WHERE s.subscribedDate BETWEEN :startDate AND :endDate")
fun countNewSubscribersByAccountBetweenDate(startDate: String, endDate: String): Long
I use Sqllite in dev and pgsql in production
The query do not return anything in dev env and it’s because of the dates
Here is how I call this method
<code>val endDate = LocalDateTime.now()
val startDate = endDate.minusDays(28)
println(subscriberRepository.countNewSubscribersByAccountBetweenDate(account, startDate, endDate))
</code>
<code>val endDate = LocalDateTime.now()
val startDate = endDate.minusDays(28)
println(subscriberRepository.countNewSubscribersByAccountBetweenDate(account, startDate, endDate))
</code>
val endDate = LocalDateTime.now()
val startDate = endDate.minusDays(28)
println(subscriberRepository.countNewSubscribersByAccountBetweenDate(account, startDate, endDate))
The result is always 0. Even if I know that I have some records between those dates
In dev environment
I have the following configuration
<code>spring.datasource:
url: jdbc:sqlite:data/db.db?date_class=TEXT
driver-class-name: org.sqlite.JDBC
spring.jpa:
properties:
hibernate:
dialect: org.hibernate.community.dialect.SQLiteDialect
database-platform: org.hibernate.community.dialect.SQLiteDialect
hibernate.ddl-auto: update
</code>
<code>spring.datasource:
url: jdbc:sqlite:data/db.db?date_class=TEXT
driver-class-name: org.sqlite.JDBC
spring.jpa:
properties:
hibernate:
dialect: org.hibernate.community.dialect.SQLiteDialect
database-platform: org.hibernate.community.dialect.SQLiteDialect
hibernate.ddl-auto: update
</code>
spring.datasource:
url: jdbc:sqlite:data/db.db?date_class=TEXT
driver-class-name: org.sqlite.JDBC
spring.jpa:
properties:
hibernate:
dialect: org.hibernate.community.dialect.SQLiteDialect
database-platform: org.hibernate.community.dialect.SQLiteDialect
hibernate.ddl-auto: update
In production environment, with pgsql, the query works.
Do you have any idea why it wouldn’t work locally with sqlite ?