Joda DateTime Field on Play Framework 2.0's Anorm

Question

I've been learning Play Framework, and the recommended way to access a database is using the build in anorm component. Thing is, there's no good support for DateTime in anorm. It's still using java.util.Date.

Is there any way to use Joda DateTime or java.sql.Timestamp in anorm?

If there's no way to use Joda or java.sql, can we add a module for that?

Cubage

@adis I think that's about JPA, and this is for anorm... – Cubage

Answer 1

update: Since play 2.3.7 this is now natively supported.

I am using the following piece of code to work with DateTime seamlessly with Anorm.

import org.joda.time._
import org.joda.time.format._
import anorm._

object AnormExtension {


val dateFormatGeneration: DateTimeFormatter = DateTimeFormat.forPattern("yyyyMMddHHmmssSS");

implicit def rowToDateTime: Column[DateTime] = Column.nonNull { (value, meta) =>
    val MetaDataItem(qualified, nullable, clazz) = meta
    value match {
        case ts: java.sql.Timestamp => Right(new DateTime(ts.getTime))
        case d: java.sql.Date => Right(new DateTime(d.getTime))
        case str: java.lang.String => Right(dateFormatGeneration.parseDateTime(str))  
        case _ => Left(TypeDoesNotMatch("Cannot convert " + value + ":" + value.asInstanceOf[AnyRef].getClass) )
    }
}

implicit val dateTimeToStatement = new ToStatement[DateTime] {
    def set(s: java.sql.PreparedStatement, index: Int, aValue: DateTime): Unit = {
        s.setTimestamp(index, new java.sql.Timestamp(aValue.withMillisOfSecond(0).getMillis()) )
    }
}

}

I think it should definitively be part of Anorm, just need to be polished and more tested. Let me know if it has helped you.

Buddha

You just put the code somewhere (e.g. to some utility package) and import it where you handle SQL statements and results by import AnormExtension._. Since the functions are defined to be implicit, Anorm will use them automatically for conversion once they are in scope. – Volteface

Any chance you could explain this please? I'm not even sure why the problem occurs, and why this solves it? Thanks! – Benavides

Reading the database is easy: DB.withConnection { implicit connection => SQL(s"select $fieldName from $tableName where ${tableName}.id = " + """{id}""").on("id" -> id).as(scalar[DateTime].singleOpt).getOrElse(new DateTime(0l)) }' But how does on do so with a given DateTimeZone`? – Blodget

Ah -- .toDateTime(DateTimeZone.UTC) which will do it's best to simply pass the original value IF the JVM is already on UTC time. (Which if you do maintenance from "other" machines that use a common/shared library could be a wildcard so better to have this explicitly set) – Blodget