How can I group-by a timestamp column into days?

[Ian Clarke]

I'd like to aggregate reporting data that is currently stored hourly in a table, such that it is also aggregated by day (I have a column that specifies the interval, HOUR, DAY, or WEEK).

Postgres has a useful function called DATE_TRUNC for this, however I am using Jooq with MySql.

How can I do this with Jooq?

Many thanks,

Ian.

[Ben Hood]

Hey Ian,

Lukas would be more specific about best practice, but this is how we solved the problem:

Field<Date> truncDay = Factory.field("trunc({0}, {1})", SQLDataType.DATE, YOUR_FIELD_GOES_HERE, inline("DD"));

HTH,

Ben

[Ian Clarke]

Interesting Ben, thank you.  Were you solving an OLAP-type problem too?  I'd be interested to see how you used truncDay in a  select and/or insert query.

Ian.

--
Ian Clarke

Blog: http://blog.locut.us/

[Ben Hood]

Hey Ian,

To see this in context (please be aware that the code is under heavy development for an upcoming new version so it is not stable production code yet):

https://github.com/lshift/diffa/blob/v2/sql-driver/src/main/java/net/lshift/diffa/sql/PartitionAwareDriver.java

This is the JOOQ-ization of the following query:

select trunc(month,'YYYY') as year, md5(group_concat(digest order by month separator '')) as digest

from (    

    select trunc(day,'MM') as month, md5(group_concat(digest order by day separator '')) as digest

    from (

        select day, md5(group_concat(digest order by bucket separator '')) as digest

        from (

            select day, bucket, md5(group_concat(version order by id separator '')) as digest

            from (

                select trunc(entry_date,'DD') as day, a.id as id, a.version as version, ceil(cast(count(*) as real) / 5) as bucket 

                from t2 a

                join t2 b ON a.entry_date = b.entry_date and a.id >= b.id

                group by trunc(entry_date,'DD'), a.id, a.version

                order by trunc(entry_date,'DD'), bucket

            )

            group by day, bucket

        ) group by day

    )

    group by trunc(day,'MM')

) group by trunc(month,'YYYY')

HTH,

Ben

[Ian Clarke]

Great, thank you Ben.

[Lukas Eder]

> This is the JOOQ-ization of the following query:

Nice to see so much jOOQ in action.

The Oracle temporal TRUNC function (probably similar to the Postgres
DATE_TRUNC function) should be implemented in jOOQ some time soon:
https://github.com/jOOQ/jOOQ/issues/470

Date-time arithmetic is difficult to implement coherently across all
databases. So far I haven't had any time to look into this any closer.

> Lukas would be more specific about best practice, but this is how we solved the problem:
>
> Field<Date> truncDay = Factory.field("trunc({0}, {1})", SQLDataType.DATE, YOUR_FIELD_GOES_HERE, inline("DD"));

I agree that such an approach is your best choice right now, before
things actually get implemented in jOOQ. Beware that you may have to
provide some additional abstraction, if you want to support the same
semantics in several SQL dialects.

Cheers
Lukas