Jooq按日/月/年比较时间戳

I am trying to perform a comparison between timestamps using jooq. I need to find out if the 2 timestamps are on the same day/month/year.

我正在尝试使用jooq执行时间戳之间的比较。我需要找出2个时间戳是否在同一天/月/年。

To do this in jpql I was using the day()/month()/year()/hour() methods.

为了在jpql中执行此操作,我使用了day()/ month()/ year()/ hour()方法。

I tried using the Field.compare(Comparator, Field) method, however this doesnt use the standard java comparator but instead uses an enum from Jooq.

我尝试使用Field.compare(Comparator,Field)方法,但是这不使用标准的java比较器,而是使用Jooq的枚举。

an example of this in jpql is

jpql中的一个例子是

SELECT s from Service s WHERE day(s.serviceDate) = day(s.createdAt) and 
month(s.serviceDate) = month(s.createdAt)

2 个解决方案

#1

The way to perform this is using the extract method and supplying a DatePart enum.

执行此操作的方法是使用extract方法并提供DatePart枚举。

For example

Condition dayCondition = SERVICE.SERVICE_DATE.extract(DatePart.DAY)
  .eq(SERVICE.CREATED_AT.extract(DatePart.DAY);

Condition monthCondition = SERVICE.SERVICE_DATE.extract(DatePart.MONTH)
  .eq(SERVICE.CREATED_AT.extract(DatePart.MONTH);


dslContext.select()
    .from(SERVICE)
    .where(dayCondtion.and(monthCondition)).fetch();

#2

Using day() and month() functions

Your own solution is absolutely correct, of course but do note there are convenience methods DSL.day() and DSL.month() that you could use as well:

当然,您自己的解决方案是绝对正确的,但请注意,您可以使用DSL.day()和DSL.month()方便的方法:

ctx.select()
   .from(SERVICE)
   .where(day(SERVICE.SERVICE_DATE).eq(day(SERVICE.CREATED_AT)))
   .and(month(SERVICE.SERVICE_DATE).eq(month(SERVICE.CREATED_AT)))
   .fetch();

Your actual question

However, your actual question was a different one:

但是,您的实际问题是另一个问题:

I need to find out if the 2 timestamps are on the same day/month/year.

我需要找出2个时间戳是否在同一天/月/年。

You should probably include the year as well. Otherwise, two timestamps on the same date in different years would match, e.g. 2017-01-01 and 2018-01-01. Here's an alternative using DSL.trunc():

你应该也可以包括这一年。否则,在不同年份的同一日期的两个时间戳将匹配,例如, 2017-01-01和2018-01-01。这是使用DSL.trunc()的替代方法:

ctx.select()
   .from(SERVICE)
   .where(trunc(SERVICE.SERVICE_DATE).eq(trunc(SERVICE.CREATED_AT)))
   .fetch();

As always, the above is assuming you're using the following static import

与往常一样,上面假设您正在使用以下静态导入

import static org.jooq.impl.DSL.*;