Anorm compare/search by java.time LocalDateTime

Question

Anorm 2.5.2 and java.time LocalDateTime as date

val users = SQL(
       s"SELECT * FROM user WHERE name={name} AND registered_date={registeredDate}").on(
        "name" -> user.name,
        "registeredDate" -> user.registeredDate
      ).executeQuery().as(userParser.*)

I found that even though I have a same name1 and same datetime(moment) in the database, the query returns empty users - can not find/match it.

So it seems It can not compare by datetime. Why it could be?

I was able to insert local-date-time correctly (see: related) into database (i mean It seems the conversion between db and scala type is on) - I.e. I use:

import anorm.ParameterMetaData._ (that extends JavaTimeParameterMetaData)

My userParser:

 val userParser: RowParser[User] = {
    get[Option[Long]]("user.id") ~
      get[String]("user.name") ~
      get[LocalDateTime]("user.registered_date") map {
      case id ~ name ~ registeredDate =>
        User(
          id  = id ,
          name = name,
          registeredDate = registeredDate
        )
    }
  }

My user:

case class User(id: Option[Long] = None,
                name: String,
                registeredDate: LocalDateTime
               )

What database are you using? Does the raw query actually work? — Michael Zajac, Oct 31 '16 at 01:57
mysql. when I query only by name - i got many users. (becase there are many users with same name in my db). i guess I need to see db-raw- queries as db receives them to see what values tt actually compares with — ses, Oct 31 '16 at 03:33
Anorm, as any other JDBC based lib, is not comparing/searching rows by itself, but just send the query to the DB, so add debug to log the parameter values and check the query with that. — cchantep, Oct 31 '16 at 08:20

score 0 · Answer 1 · edited May 23 '17 at 11:55

Ok. My bad.

It is about MYSQL and precision of TIMESTAMP http://dev.mysql.com/doc/refman/5.5/en/datetime.html

So, in my case:

CREATE TABLE `user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `registered_date` TIMESTAMP(6) NOT NULL,
  PRIMARY KEY (`id`));

here: Timestamp with a millisecond precision: How to save them in MySQL

I had just to declare it with TIMESTAMP(6) - that would include milliseconds as well.

Because in my test:

  "The User Repository" should "not create new user if it already exists" in {

    val someTimeAgo = LocalDateTime.now
    val userWithoultId1 = User(name = "name1", registeredDate = someTimeAgo)
    val maybeUserWithId1 = userRepository.findOrCreate(userWithoultId1)
    val maybeUserWithId2 = userRepository.findOrCreate(userWithoultId1)
    maybeUserWithId1.get.id should === (maybeUserWithId2.get.id)

LocalDateTime.now includes milieseconds as well, obviously

So I still love Anorm. (it is just me..) :)

Anorm compare/search by java.time LocalDateTime

1 Answers1

Linked