Hi Mark, Regarding your questions:
1. There's a flag to treat Oracle DATE types like TIMESTAMP types by the code generator. The flag is <dateAsTimestamp/>. I'm not sure if that helps, in your case. Another option is to use <forcedType/> to rewrite your data types to something more appropriate, in the code generator. I understand that you're using other databases than Oracle as well, so using the java.sql.Timestamp (or java.time.LocalDateTime) type is really the most appropriate in this case. We're not going to overload timestampDiff() (and the myriad other date-time related methods) with an Oracle version where the implied meaning of DATE is TIMESTAMP(0). Yes, we're all suffering from Oracle's historic DATE data type, which does not conform to the SQL standard. But using java.sql.Date is not going to work, because ojdbc is standards compliant again, and will (usually) truncate the hours/minutes/seconds. In fact, how do you even construct a java.sql.Date with hours/minutes/seconds? You could pass a unix timestamp (long) to it, but would you be certain that you got time zones correctly? The only reasonable way to construct a java.sql.Date is by calling valueOf(), and that results in a standards-compliant date. I'm sure you didn't mean to overload the methods for usage with oracle.sql.DATE - that would be rather ugly :) If all else fails, you can always use a custom data type binding, where you have dialect specific bindings for each SQL dialect that you wish to support: https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings 2. Use a local variable for the expression: Field<DayToSecond> timeDiff = timestampDiff(TEST.ARRIVAL, lag(TEST.DEPARTURE).over().orderBy(TEST.ID)).as("time_diff"); Result<Record2<BigInteger, DayToSecond>> record = create .select(TEST.ID, timeDiff) .from(TEST) .orderBy(TEST.ID) .fetch(); And then: for (int i = 0; i < record.size(); i++) { Record2<BigInteger, DayToSecond> entry = record.get(i); DayToSecond d = entry.get(timeDiff); System.out.println(entry.get(TEST.ID) + ": " + (d != null ? d.getTotalMinutes() : null)); } I hope this helps, Lukas -- 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.
