在通过 Slick 比较 DateTime 时需要帮助 [英] Need help on comparing DateTime through Slick

问题描述

代码:

 def getDatasetStats(startDate: DateTime, endDate: DateTime) = {val query = for(created <- datasets) yield created.createdOndb.run(query.filter(d => d >= startDate && d <= endDate).size.result)}

表格:

protected class Datasets(tag: Tag) extends Table[SqlDataset](tag, "datasets") {//格式:关闭def id = column[UUID]("id", O.PrimaryKey)def name = column[String]("name")def createdOn = column[DateTime]("created_on")def updatedOn = column[Option[DateTime]]("updated_on")def isPublic = column[Boolean]("public")def * = (id, name, createdOn, isPublic, updatedOn) <>((SqlDataset.apply _).tupled, SqlDataset.unapply)隐式 def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](日期时间 =>新时间戳(dateTime.getMillis)，时间戳 =>新日期时间(timeStamp.getTime))//格式:开}

我尝试的上述解决方案无法正常工作.此外，我无法使用 isBefore 或 isAfter，因为我从数据库获取 Rep[DateTime].我需要关于在结果之上应用日期范围过滤器的帮助.

解决方案

isBefore 和 isAfter 不起作用.您必须使用 <、<= 和 >、>=.

Slick 建立在 JDBC 之上.JDBC 仅理解 java.sql.Timestamp.因此，为 joda DateTime 提供 implicit 映射列类型.

 隐式 def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](日期时间 =>新时间戳(dateTime.getMillis)，时间戳 =>新日期时间(timeStamp.getTime))

现在你可以使用<、<=、>和>=

> 等价于 isAfter 而 < 等价于 isBefore.

在处理 DateTime

的范围内具有 implicit

表格

protected class Datasets(tag: Tag) extends Table[SqlDataset](tag, "datasets") {隐式 def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](日期时间 =>新时间戳(dateTime.getMillis)，时间戳 =>新日期时间(timeStamp.getTime))def id = column[UUID]("id", O.PrimaryKey)def name = column[String]("name")def createdOn = column[DateTime]("created_on")def updatedOn = column[Option[DateTime]]("updated_on")def isPublic = column[Boolean]("public")def * = (id, name, createdOn, isPublic, updatedOn) <>((SqlDataset.apply _).tupled, SqlDataset.unapply)}

方法

def getDatasetStats(startDate: DateTime, endDate: DateTime) = {隐式 def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](日期时间 =>新时间戳(dateTime.getMillis)，时间戳 =>新日期时间(timeStamp.getTime))db.run(datasets.map(_.createdOn).filter(d => d >= startDate && d <= endDate).size.result)}

Code:

 def getDatasetStats(startDate: DateTime, endDate: DateTime) = {
    val query = for(created <- datasets) yield created.createdOn
    db.run(query.filter(d => d >= startDate && d <= endDate).size.result)   
  }

Table:

protected class Datasets(tag: Tag) extends Table[SqlDataset](tag, "datasets") {
    // format: OFF
    def id = column[UUID]("id", O.PrimaryKey)

    def name = column[String]("name")

    def createdOn = column[DateTime]("created_on")

    def updatedOn = column[Option[DateTime]]("updated_on")

    def isPublic = column[Boolean]("public")

    def * = (id, name, createdOn, isPublic, updatedOn) <>
      ((SqlDataset.apply _).tupled, SqlDataset.unapply)

    implicit def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](
      dateTime => new Timestamp(dateTime.getMillis),
      timeStamp => new DateTime(timeStamp.getTime)
    )

    // format: ON
  }

The above solution i have tried is not working properly. Also i could not use isBefore or isAfter since i am getting Rep[DateTime] from DB. I need help on applying the date range filter on top of the result.

解决方案

isBefore and isAfter does not work. You have to use <, <=, and >, >=.

Slick is built on top of JDBC. JDBC understands java.sql.Timestamp only. So provide implicit mapped column type for joda DateTime.

 implicit def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](
    dateTime => new Timestamp(dateTime.getMillis),
    timeStamp => new DateTime(timeStamp.getTime)
  )

Now you can use <, <=, > and >=

> is equivalent to isAfter and < is equivalent to isBefore.

Have implicit in the scope where ever you are dealing with DateTime

table

protected class Datasets(tag: Tag) extends Table[SqlDataset](tag, "datasets") {

implicit def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](
      dateTime => new Timestamp(dateTime.getMillis),
      timeStamp => new DateTime(timeStamp.getTime)
    )

def id = column[UUID]("id", O.PrimaryKey)

def name = column[String]("name")

def createdOn = column[DateTime]("created_on")

def updatedOn = column[Option[DateTime]]("updated_on")

def isPublic = column[Boolean]("public")

def * = (id, name, createdOn, isPublic, updatedOn) <>
  ((SqlDataset.apply _).tupled, SqlDataset.unapply)

}

Method

def getDatasetStats(startDate: DateTime, endDate: DateTime) = {

implicit def jodaTimeMapping: BaseColumnType[DateTime] = MappedColumnType.base[DateTime, Timestamp](
          dateTime => new Timestamp(dateTime.getMillis),
          timeStamp => new DateTime(timeStamp.getTime)
        )

  db.run(datasets.map(_.createdOn).filter(d => d >= startDate && d <= endDate).size.result)   
}

这篇关于在通过 Slick 比较 DateTime 时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！