SQL查询无法返回当前月份数据的问题求助

核心问题原因 #

你的查询无法返回当前月份数据，本质是日期类型不匹配导致的范围截断：

• date_created 是 datetime/timestamp 类型（包含时分秒），但你传入的 dateUntil 是 LocalDate 类型，JPA 会自动将其转换为 yyyy-MM-dd 00:00:00。
• 使用 BETWEEN :dateFrom AND :dateUntil 时，实际只会查询到 dateUntil 当天0点之前的数据，也就是前一天23:59:59的订单，当天的所有订单都被排除了。

另外你尝试的第二个查询存在两个致命问题：

1. 使用 MONTH()、YEAR() 函数会导致 date_created 字段上的索引失效，查询性能暴跌；
2. 若查询范围跨年度（比如2023-11到2024-02），MONTH() BETWEEN 11 AND 2 逻辑不成立，会直接返回空结果。

修正后的可行方案 #

方案1：调整SQL的日期范围条件（推荐） #

直接修改查询条件，让范围包含 dateUntil 当天的所有时间，同时保留索引可用性：

@Query(value = "SELECT o.restaurant_id as restaurantId, YEAR(o.date_created) as year, MONTH(o.date_created) as month, "
             + " count(1) as tpn, sum(o.total) as tpv "
             + " FROM ordersDB.orders o "
             + " WHERE o.provider_id = :providerId "
             + " AND o.status != 2"
             + " AND o.restaurant_id = :restaurantId "
             + " AND o.date_created >= :dateFrom "
             + " AND o.date_created < DATE_ADD(:dateUntil, INTERVAL 1 DAY) "
             + " GROUP BY 1,2,3 ", nativeQuery = true)
List<IReportOrderEvolutionDto> getRestaurantProviderEvolution(Long providerId, Long restaurantId, LocalDate dateFrom, LocalDate dateUntil);

逻辑说明：

• o.date_created >= :dateFrom：包含起始日期的0点及之后的数据；
• o.date_created < DATE_ADD(:dateUntil, INTERVAL 1 DAY)：包含结束日期的23:59:59及之前的数据（因为第二天的0点之前的所有时间都属于当前日期）。

方案2：在Java层处理参数类型 #

将 dateUntil 转换为包含当天最后一秒的 LocalDateTime，再传入查询：

// 调用方法时处理参数
LocalDate dateUntil = ...;
LocalDateTime endDateTime = dateUntil.atTime(LocalTime.MAX);

// 修改Repository方法的参数类型
@Query(value = "SELECT o.restaurant_id as restaurantId, YEAR(o.date_created) as year, MONTH(o.date_created) as month, "
             + " count(1) as tpn, sum(o.total) as tpv "
             + " FROM ordersDB.orders o "
             + " WHERE o.provider_id = :providerId "
             + " AND o.status != 2"
             + " AND o.restaurant_id = :restaurantId "
             + " AND o.date_created BETWEEN :dateFrom AND :endDateTime "
             + " GROUP BY 1,2,3 ", nativeQuery = true)
List<IReportOrderEvolutionDto> getRestaurantProviderEvolution(Long providerId, Long restaurantId, LocalDate dateFrom, LocalDateTime endDateTime);

注意：若数据库有时区配置，需确保Java的时区与数据库一致，避免时间转换偏差。

错误查询的修复（不推荐） #

如果一定要使用月份/年份函数查询，需修正逻辑避免跨年度问题，但仍会损失索引性能：

SELECT o.restaurant_id as restaurantId, YEAR(o.date_created) as year, MONTH(o.date_created) as month, 
       count(1) as tpn, sum(o.total) as tpv 
FROM ordersDB.orders o 
WHERE o.provider_id = 1
  AND o.status != 2
  AND o.restaurant_id = 4
  -- 起始条件：年份大于起始年，或同年且月份大于等于起始月
  AND (YEAR(o.date_created) > YEAR('2023-05-01') 
       OR (YEAR(o.date_created) = YEAR('2023-05-01') AND MONTH(o.date_created) >= MONTH('2023-05-01')))
  -- 结束条件：年份小于结束年，或同年且月份小于等于结束月
  AND (YEAR(o.date_created) < YEAR('2023-10-31') 
       OR (YEAR(o.date_created) = YEAR('2023-10-31') AND MONTH(o.date_created) <= MONTH('2023-10-31')))
GROUP BY 1,2,3

内容的提问来源于stack exchange，提问作者1508.dev