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 On Thu, Dec 13, 2012 at 5:47 PM, Ian Clarke <[email protected]> wrote: > 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. > > > On Thu, Dec 13, 2012 at 11:46 AM, Ben Hood <[email protected]> wrote: > >> 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 >> >> >> On Thu, Dec 13, 2012 at 4:34 PM, Ian Clarke <[email protected]> wrote: >> >>> 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. >>> >> >> > > > -- > Ian Clarke > Blog: http://blog.locut.us/ > >
