Spring Boot Sqlite查询日期

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

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);

@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);

@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);

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();
}

    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);

Spring Boot Sqlite查询日期

1 个回答