mysql - 使用 JPA 和 Joda-Time 根据 DateTime 列从 MySQL 获取记录，忽略时间部分

Question

如何使用 JPA 过滤 MySQL 数据库中的行，忽略 MySQL 中给定日期时间字段的时间部分？

例如，以下代码段计算数据库表中位于 MySQL 中 DateTime 类型列中给定的两个日期之间的行数。

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Long>criteriaQuery=criteriaBuilder.createQuery(Long.class);
Root<Discount> root = criteriaQuery.from(entityManager.getMetamodel().entity(Discount.class));
criteriaQuery.select(criteriaBuilder.countDistinct(root));

DateTimeFormatter dateTimeFormatter=DateTimeFormat.forPattern("dd-MMM-yyyy hh:mm:ss aa");
DateTime firstDate = dateTimeFormatter.parseDateTime("01-Oct-2013 11:34:26 AM").withZone(DateTimeZone.UTC);
DateTime secondDate = dateTimeFormatter.parseDateTime("31-Oct-2013 09:22:23 PM").withZone(DateTimeZone.UTC);

criteriaQuery.where(criteriaBuilder.between(root.get(Discount_.discountStartDate), firstDate, secondDate));
Long rowCount = entityManager.createQuery(criteriaQuery).getSingleResult();

两个参数firstDate和secondDate将依次是动态的。

如何重写此查询，以便比较不包括委托(delegate)给 MySQL 的 SQL 查询中的时间部分。

实体Discount中的列discount_start_date指定如下。

@Column(name = "discount_start_date")
@Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime discountStartDate;

Answer 1

看来你工作太辛苦了。

(a) 显然，MySQL 提供了 DATE() 提取日期的日期部分的函数时间字段。 (我是 Postgres 人，并且不了解 MySQL。)您可以寻求一种使用该函数调用作为查询的一部分的方法。但我猜如果您首先通过计算 Joda-Time 来获得开始和停止时间，性能会更快。在执行 SQL 查询之前使用 Java 编写，如下所示。

(b) 为什么不使用简单的 SQL 查询(两个条件 SELECT)来完成此操作？

在伪代码中:

Find Discount records that go into effect from the moment this month starts up until the moment the next month starts.

使用 Java 和 Joda-Time 为您提供开始值和停止值。

org.joda.time.DateTime startOfThisMonth = new org.joda.time.DateTime().dayOfMonth().withMinimumValue().withTimeAtStartOfDay();
org.joda.time.DateTime startofNextMonth = startOfThisMonth.plusMonths( 1 ).dayOfMonth().withMinimumValue().withTimeAtStartOfDay();

注意:以上代码使用默认时区。您应该在构造函数中指定时区。

MySql 似乎缺乏复杂的时区等时间日期处理。因此我想您会将这些时区 DateTime 对象转换为 UTC。

org.joda.time.DateTime startOfThisMonthInUtc = startOfThisMonth.toDateTime( org.joda.time.DateTimeZone.UTC );
org.joda.time.DateTime startofNextMonthInUtc = startofNextMonth.toDateTime( org.joda.time.DateTimeZone.UTC );

然后执行您所做的操作来获取 MySQL 的日期时间值。

然后形成一个如下所示的查询...(请注意使用 >= 与不带等号的 <。)

SELECT title_, amount_, start_date_ 
FROM discount_
WHERE discount_.start_datetime_ >= startOfThisMonthFromJodaTime
AND discount_.start_datetime_ < startOfNextMonthFromJodaTime
;

在处理日期和时间时，通常最好处理一天的第一时刻、一月第一天的第一时刻等，而不是尝试查找最后时刻或结束时间。因此，我的查询基于查找其值达到(但不包括)我感兴趣的时间范围之后那一刻的行的想法。