Issues with timestampDiff

[Mark L.]

Result<Record2<BigInteger, DayToSecond>> record = create

.select(

   TEST.ID, timestampDiff(TEST.ARRIVAL, lag(TEST.DEPARTURE).over().orderBy(TEST.ID)).as("time_diff")

       ).from(TEST)

.orderBy(TEST.ID)

.fetch();

Hi,

I am testing the query above. But I found 2 difficulties:

1.) timestampDiff only takes TIMESTAMP columns as parameters. But mostly we are working with Oracle's DATE types. These are not working here. Would it be possible to overload the timestampDiff method for the DATE types?

2.) I am using an alias to rename the new calculated column - because it is has a new semantic meaning and a new data type. But now jOOQ is losing the type safety. On fetching I have to do an explicit type cast to DayToSecond if I want to fetch the "time_diff" column:

        for (int i = 0; i < record.size(); i++) {
            Record2<BigInteger, DayToSecond> entry = record.get(i);
            DayToSecond timeDiff = (DayToSecond)entry.get("time_diff");
            System.out.println(entry.get(TEST.ID) + ": " + (timeDiff != null ? timeDiff.getTotalMinutes() : null));
        }

Is there a better way?

Greets, Mark

[Mark L.]

If it makes clearer, here is a sample table:

CREATE TABLE schema.test
    (id integer, arrival timestamp, departure timestamp)
;  

INSERT INTO schema.test
    (id, arrival, departure)
VALUES
    (0, NULL, '2000-01-01 22:00:00.000'),
    (1, '2000-01-01 22:30:00.000', '2000-01-01 22:35:00.000'),
    (2, '2000-01-01 23:10:00.000', '2000-01-01 23:10:00.000'),
    (3, '2000-01-01 23:50:00.000', '2000-01-01 23:55:00.000'),
    (4, '2000-01-02 00:10:00.000', '2000-01-02 00:10:00.000'),
    (5, '2000-01-02 01:30:00.000', NULL)
;

[Lukas Eder]

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

[Mark L.]

I see. Great!