Squeryl: How to create an aggregate query using postgres' date_trunc() function on a date column

Question

Given following entity class and a postgres database with a related table:

case class Statistics(name: String, 
                      measure: Long, 
                      datetime: Timestamp = new Timestamp(System.currentTimeMillis))

How can I construct a Squeryl aggregate query that returns the count of measures or their accumulated values per day or week, basically resulting in SQL statements similar to:

select count(*), date_trunc('day',datetime) from stats
       group by date_trunc('day',datetime);
select sum(*), date_trunc('day',datetime) from stats
       group by date_trunc('day',datetime);

using postgres' date_trunc function that is not directly accessible from Squeryl.

Erich Schreiner · Accepted Answer · 2014-06-24T20:26:22.503

The desired query can be produced using a custom function mapping:

class DateTrunc(span: String, 
                e: DateExpression[Timestamp], 
                m: OutMapper[Timestamp])
extends FunctionNode[Timestamp]("date_trunc", 
                     Some(m), 
                     Seq(new TokenExpressionNode("'"+span+"'"), e))
with DateExpression[Timestamp]

def dateTrunc(span: String, 
              e: DateExpression[Timestamp])
             (implicit m: OutMapper[Timestamp]) = new DateTrunc(span,e,m)

This can then be used in the actual query, e.g.:

def historyQuery(name: String, 
                          span: String = "day", 
                          pageSize: Int = 10) = from(table) (
    stats =>
        where(stats.name === name)
        groupBy(dateTrunc(span,stats.datetime))
        compute(count)
        orderBy(dateTrunc(span,stats.datetime) desc)
) page(0,pageSize)

Squeryl: How to create an aggregate query using postgres' date_trunc() function on a date column

1 Answers1