当前位置: 首页 > 知识库问答 >
问题:

Spring Boot Sqlite查询日期

赵正雅
2023-03-14

我有一个使用Sqlite数据库的树莓Pi温度记录器,它与我的嵌入式C Civetweb应用程序配合得很好。

我想我会有一些乐趣,尝试把一些东西与Spring靴连接起来。我可以进行一些查询,但几乎没有任何查询日期的方法。

我使用org.xerialsqlite jdbc3.36.0.3和com.enigmabridgehibernate4-sqlite方言0.1.2设置sqlite数据源。

在我的Pi上,返回数据并显示最后一天的温度图的主要查询是:

SELECT timestamp, temp, id FROM temps WHERE timestamp > datetime('now', 'localtime', '-1 days');

但这种类型的查询似乎不适用于JPA,因为查询天数在单引号字符串文本内,所以所有参数都按原样传递。如果我直接在查询中指定日期,它将返回数据,但我希望将其作为参数传递。

所以我尝试在Spring Boot中查询两个日期,如下所示:

LocalDate end = LocalDate.now();
LocalDate start = end.minusDays(2);

return temperatureRepository.findTemperaturesBetweenDates(start, end);

在存储库中使用此选项:

@Query("SELECT op FROM Temperature op WHERE timestamp between :startDate AND :endDate")
List<Temperature> findTemperaturesBetweenDates(@Param(value="startDate") LocalDate startDate, @Param(value="endDate") LocalDate endDate);

此查询不返回任何结果。同样,在查询中直接用单引号指定日期,而不使用参数,这样会返回数据。我也尝试了nativeQuery,但结果相同。

将Hibernate上的日志记录级别更改为trace,我可以看到正在使用的参数,它似乎正在将日期转换为纪元时间。

/* SELECT
    op 
FROM
    Temperature op 
WHERE
    timestamp between :startDate AND :endDate */ select
        temperatur0_.timestamp as timestam1_0_,
        temperatur0_.id as id2_0_,
        temperatur0_.temp as temp3_0_ 
    from
        temps temperatur0_ 
    where
        timestamp between ? and ? Hibernate: 
/* SELECT
    op 
FROM
    Temperature op 
WHERE
    timestamp between :startDate AND :endDate */ select
        temperatur0_.timestamp as timestam1_0_,
        temperatur0_.id as id2_0_,
        temperatur0_.temp as temp3_0_ 
    from
        temps temperatur0_ 
    where
        timestamp between ? and ?

2022-06-06 23:18:45.393 DEBUG 9328-[nio-8080-exec-4]o.s.j.d.DriverManager数据源:创建新的JDBC DriverManager连接到[JDBC:sqlite:src/main/resources/sqltemlog.db]2022-06-06 23:18:45.394 TRACE 9328-[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl:注册语句[/*从温度op中选择op,其中时间戳介于:startDate和:endDate之间/选择Temperature0\u。时间戳为timestam1\u 0\u,Temperature0\u。id为id2\u 0\u,Temperature0\u。temp为temp3\u 0\u。从temps Temperature0\u,其中时间戳介于?和?参数=null之间]2022-06-06 23:18:45.394跟踪9328-[nio-8080-exec-4]o.h.e.jdbc。内部的JdbcCoordinatorImpl:注册上一条查询语句[/SELECT op FROM Temperature op WHERE timestamp between:startDate AND:endDate/SELECT temperature0\u。timestamp as timestam1\u 0\u,temperature0\u。id as id2\u 0\u,temperature0\u。temp as temp3\u 0\u,FROM temps temperature0\u,WHERE timestamp between?AND?
parameters=null]2022-06-06 23:18:45.394 TRACE 9328---[nio-8080-exec-4]o.h.type。描述符。sql。BasicBinder:绑定参数[1]为[日期]-[2022-06-04]2022-06-06 23:18:45.394跟踪9328-[nio-8080-exec-4]o.h.type。描述符。sql。BasicBinder:绑定参数[2]为[日期]-[2022-06-06]2022-06-06 23:18:45.394 TRACE 9328-[nio-8080-exec-4]org。冬眠装载机。Loader:Bound[3]parameters total 2022-06-06 23:18:45.394 TRACE 9328---[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl:注册结果集[org.sqlite.jdbc4]。JDBC4ResultSet@23105e31]2022-06-06 23:18:45.394跟踪9328---[nio-8080-exec-4]组织。冬眠装载机。加载器:处理结果集2022-06-06 23:18:45.394跟踪9328---[nio-8080-exec-4]组织。冬眠装载机。加载器:完成处理结果集(0行)2022-06-06 23:18:45.394跟踪9328---[nio-8080-exec-4]组织。冬眠装载机。加载器:水合对象总数:0 2022-06-06 23:18:45.395跟踪9328---[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl:释放语句[/从温度op中选择op,其中时间戳介于:startDate和:endDate之间/选择Temperature0\uTimestamp为timestam1\u0\u0,Temperature0\uID为id2\u 0\u0,Temperature0\uTemp为temp3\u 0\u0,其中时间戳介于?和?
参数=[1654297200000,1654470000000]]2022-06-06 23:18:45.395 TRACE 9328---[nio-8080-exec-4]o.h.r.j.i.ResourceRegistrate RyStandardImpl:关闭结果集[org.sqlite.jdbc4。JDBC4ResultSet@23105e31]2022-06-06 23:18:45.395跟踪9328-[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl:关闭准备好的语句[/从温度op中选择op,其中时间戳介于:startDate和:endDate之间*/选择temperature0\uTimestamp作为timestam1\u0\u0,temperature0\uID作为id2\u0\u0,temperature0\uTemp作为temp3\u0\uTemps Tempperature0\u0\u0,其中时间戳介于?和?
参数=[1654297200000, 1654470000000]]

我设法让它工作的唯一方法是在查询中使用unixepoch,并在Sqlite中转换参数:

@Query(value =("select timestamp, id, temp from temps where timestamp between DATETIME(ROUND(:startDate / 1000), 'unixepoch') AND DATETIME(ROUND(:endDate / 1000), 'unixepoch')"), nativeQuery = true)
List<Temperature> findTemperaturesTest(@Param(value="startDate") LocalDate startDate, @Param(value="endDate") LocalDate endDate);

有什么想法如何让日期查询在Spring Boot和Sqlite中正常工作吗?谢谢

编辑:

好的,所以只是设法让-X天部分工作,但是我确实有其他查询,结合 /-天和日期,但仍然没有运气正确地将日期传递给查询。

@Query("SELECT op FROM Temperature op WHERE id between 1 and 4 AND timestamp > datetime('now', 'localtime', '-' || :days || ' days')")
List<Temperature> findTemperaturesMinusDays(@Param(value="days") int days);

共有1个答案

陶飞英
2023-03-14

对,这一切似乎都归结于Sqlite存储日期的方式。我的数据库一直将时间戳字段存储为字符串,如“2022-06-07 23:44:19”,大多数数据库将日期存储为unix时间。

没问题,我只告诉它在查询数据库时将LocalDateTime转换为字符串。嗯,也许没那么容易。我尝试了@Converter、@Type,还有一些我忘记的东西。这些都不起作用。

我的第一次半成功尝试是将类中的数据类型更改为String,并在getter中进行转换。但这意味着@Query参数也必须作为字符串传递。不太好。

因此,我的解决方案是一个自定义存储库,它将LocalDateTime转换为字符串,并将其设置为查询中的参数。

public class TemperatureRepositoryImpl implements TemperatureRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Temperature> findTemperaturesBetweenDates(LocalDateTime startDate, LocalDateTime endDate) {
        Query query = entityManager.createNativeQuery("SELECT timestamp, temp, id FROM temps WHERE timestamp BETWEEN :startDate AND :endDate");

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String startDateString = startDate.format(formatter);
        String endDateString = endDate.format(formatter);

        query.setParameter("startDate", startDateString);
        query.setParameter("endDate", endDateString);

        return query.getResultList();
    }
}

编辑

差点受够了。有几个问题-接口存储库不起作用,只有实现,日期不包括时间。最后,使用TypedQuery:

    public List<Temperature> findTemperaturesBetweenDates(LocalDateTime startDate, LocalDateTime endDate) {
        TypedQuery<Temperature> query = entityManager.createQuery("SELECT op FROM Temperature op WHERE timestamp BETWEEN date(:startDate) AND date(:endDate)", Temperature.class);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String startDateString = startDate.format(formatter);
        String endDateString = endDate.format(formatter);

        query.setParameter("startDate", startDateString);
        query.setParameter("endDate", endDateString);

        return query.getResultList();
    }
 类似资料:
  • 本实例使用有关日期处理和日期格式化的类实现一个日期查询的功能,即查询指定日期所在周的周一日期、两个指定日期间相差的天数和指定日期为所在周的星期几的日期 3 个功能。 从功能上来看,本实例至少需要定义 3 个方法,分别完成:获取指定日期所在周的周一日期、获取两个指定日期的相差天数以及获取指定日期为所在周的星期几的日期这 3 个功能,同时需要定义 Date 类型的日期与 String 类型之间的转换方

  • 如果日期相同,则使用进行查询将无法正常工作。它更像是。 但如果我给query param加上1秒,它就会工作。 下面是示例查询; 我有一个文档,它是时间戳。 此查询与日期为的文档相匹配,如果我将查询时间戳增加一个,如。 我正在使用mongo控制台,但我在php中遇到了与MongoDate相同的问题 编辑:查询的输出 edit2:问题是mongo的php扩展。它记录了“当文档发送到/从数据库发送时,

  • 慢查询日志是 Redis 提供的一个用于观察系统性能的功能, 这个功能的实现非常简单, 这里我们也简单地讲解一下。 本章先介绍和慢查询功能相关的数据结构和变量, 然后介绍 Redis 是如何记录命令的执行时间, 以及如何为执行超过限制事件的命令记录慢查询日志的。 相关数据结构 每条慢查询日志都以一个 slowlog.h/slowlogEntry 结构定义: typedef struct slowl

  • 问题内容: 我想创建一个Ecto查询,该查询按表的年龄(即“最小年龄(月)->最大年龄(月)”)过滤表中的记录。 一种简单的方法是Ecto功能: 这样做的问题是,并非所有孩子都在同一时区,当然也不是所有孩子都在同一时区。该查询将非常接近,但没有发现(有些会在一天之内关闭)。 我一直在尝试使用PostgreSQL的功能来使此查询工作。我可以使用SQL客户端使其正常工作,但是在尝试对片段中的值进行插值

  • 在一个日期列中有一个表,在同一个日期的不同时间有这么多总计的条目,但我不担心时间。我需要找到某些日期所有总额,随着用户名。当我使用下面的查询。它显示语法错误 选择用户名,总额,从账单日期='2013-04-12' 在上面的查询日期是列名,请帮我。

  • 问题内容: 我在集合中将值存储为java.util.Date(),但是当我查询以获取两个特定日期之间的值时,最终得到的值超出了范围。这是我的代码: 插入 查询 当我在和之间查询时(基本上是fromDate = toDate),我得到的日期绝对不可能的对象。我在这里想念什么? 问题答案: 您正在执行的操作是仅在键覆盖中使用丢失运算符进行查询。您想要的是:

  • 因此,我想运行一个查询来检索过去15天的所有与dateofActivity有关的信息。所以我使用了以下查询: 然后我注意到返回的记录并不局限于过去15天的所有活动。然后我尝试运行查询: 这并不限制查询记录。返回的记录包含了2020年12/27/的一些数据。我在查询中哪里做错了? 另外,基于这个dateofActivity字段检索过去一个月的结果的最佳方法是什么?例如,如果我想在每月的第一天运行此报

  • 问题内容: 我试图找出如何用Mongoose做特定的查询。我在mongodb中有这样的东西: 我想获取实际日期和modificationDate之间的差异大于5天的所有对象。 问题答案: 计算5天的截止时间,然后使用运算符和计算出的截止值执行: