Average date difference in QueryDSL

Question

I have an entity with a Date object representing last activity. I would like to query the average idle time. So in SQL something like:

SELECT AVG(NOW() - idle_date) FROM mytable WHERE ....

I tried to calculate the difference like this:

query.singleResult(
    Expressions.dateOperation(
        Long.class, 
        Ops.DateTimeOps.DIFF_SECONDS,
        DateExpression.currentDate(),
        myTable.idleDate
    )
);

But that does not allow me to do an average on the result, only min and max.

How can I express this average on a date/time difference (in seconds) ?

Answer 1

I believe you could use a NumberTemplate object; something along these lines should do what you need: NumberTemplate.create(Double.class, "AVG(NOW() - {0})", myTable.idleDate);