Hello,
the following code snippet does fail against Postgres:
max(greatest(dateDiff(table.DATE1, dateAdd(table.DATE2, inline(10),
DatePart.DAY)), inline(0)))
The generated SQL is the following:
max(greatest(table.date1 - (table.date2 + (10 || ' day')::interval),0))
The reported error is:
ERROR: GREATEST types interval and integer cannot be matched
The problem is the fact, that Postgres returns Timestamp when an interval
is added to the DATE type, so during the SQL execution in postgres the date
operation returns interval instead of integer (as a result of a DATE -
TIMESTAMP operation).
Unfortunately any attempt to cast the Timestamp to Date is ignored by JOOQ
during SQL generation, as the dateAdd function returned value is
Field<Date> so the cast is removed/ignored when SQL is generated.
Probably fix: In case of Postgres the generated SQL should add just the
integer to the date (do not use intervals).
Workaround:
You can use a simple custom field when You do use only Postgres and cross
DB compatibility is not required:
public class AddDaysToDateCustomField extends CustomField<Date> {
private final Field<Date> field;
private int days;
public AddDaysToDateCustomField(Field<Date> field, int days) {
super(field.getName(), DATE);
this.field = field;
this.days = days;
}
@Override
public void accept(Context<?> ctx) {
ctx.visit(delegate(ctx.configuration()));
}
private QueryPart delegate(Configuration configuration) {
if (days < 0) {
return DSL.field("({0} - {1})", DATE, field, -1 * days);
} else {
return DSL.field("({0} + {1})", DATE, field, days);
}
}
}
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
