{"action":"created","issue":{"url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/issues/34","labels_url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/issues/34/labels{/name}","comments_url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/issues/34/comments","events_url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/issues/34/events","html_url":"https://github.com/tototoshi/slick-joda-mapper/pull/34","id":98276662,"number":34,"title":"`DateTime` drops timezone in select","user":{"login":"blast-hardcheese","id":278900,"avatar_url":"https://avatars.githubusercontent.com/u/278900?v=3","gravatar_id":"","url":"https://api.github.com/users/blast-hardcheese","html_url":"https://github.com/blast-hardcheese","followers_url":"https://api.github.com/users/blast-hardcheese/followers","following_url":"https://api.github.com/users/blast-hardcheese/following{/other_user}","gists_url":"https://api.github.com/users/blast-hardcheese/gists{/gist_id}","starred_url":"https://api.github.com/users/blast-hardcheese/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/blast-hardcheese/subscriptions","organizations_url":"https://api.github.com/users/blast-hardcheese/orgs","repos_url":"https://api.github.com/users/blast-hardcheese/repos","events_url":"https://api.github.com/users/blast-hardcheese/events{/privacy}","received_events_url":"https://api.github.com/users/blast-hardcheese/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-07-30T22:15:19Z","updated_at":"2015-10-14T00:26:14Z","closed_at":"2015-10-09T16:58:14Z","pull_request":{"url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/pulls/34","html_url":"https://github.com/tototoshi/slick-joda-mapper/pull/34","diff_url":"https://github.com/tototoshi/slick-joda-mapper/pull/34.diff","patch_url":"https://github.com/tototoshi/slick-joda-mapper/pull/34.patch"},"body":"So, unfortunately, it seems like `DateTime`'s `valueToSQLLiteral` drops the timezone when converting to a JDBC timestamp:\r\n\r\n    val now = DateTime.now\r\n\r\n    val driver = play.api.db.slick.Config.driver\r\n    val jdtm = new JodaDateTimeMapper(driver)\r\n    println(s\"here: ${jdtm.TypeMapper.valueToSQLLiteral(now)}\")\r\n    // Prints: {ts '2015-07-30 12:05:33.652'}\r\n\r\nUnfortunately, this causes problems with even the most basic of database round-tripping:\r\n\r\n    println(\"  > delete\")\r\n    entries.delete\r\n    println(\"  < delete\")\r\n\r\n    println(\"  > insert\")\r\n    entries.insert(Entry(created_on = now)\r\n    println(\"  < insert\")\r\n\r\n    println(\"  > select\")\r\n    val q = (\r\n      entries\r\n        .filter(_.created_on <= now)\r\n        .map(_.id)\r\n    )\r\n\r\n    println(q.selectStatement)\r\n    // prints: select x2.\"id\" from (x3.\"id\" as \"id\" from \"feed_entry\" x3 where x3.\"created_on\" <= {ts '2015-07-30 12:05:33.652'}) x2\r\n\r\n    val res = q.run\r\n    println(\"  < select\")\r\n\r\n    println(s\"res: $res\")\r\n    // prints Vector()\r\n\r\n[Some digging](http://stackoverflow.com/questions/9202857/timezones-in-sql-date-vs-java-sql-date#answer-9305468) suggests that `java.sql.Timestamp` is a point in time _without_ a timezone, so calling `toSqlType(value).toString` drops the timezone, then wrapping that in the JDBC encoding of `{ts '...'}` further ensures that the database knows of it only as \"a point of time\". Without the timezone attached, database configuration enters the game, and errors occur.\r\n\r\nMy proposal here is to continue @dk8996's work for string encoding as well, using Joda's `DateTimeFormatterBuilder` to build a `timestamp with time zone`, representing the `DateTime`. This solves the problems I was encountering, although I'm not sure it's portable.\r\n\r\nComments are definitely welcome."},"comment":{"url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/issues/comments/147890972","html_url":"https://github.com/tototoshi/slick-joda-mapper/pull/34#issuecomment-147890972","issue_url":"https://api.github.com/repos/tototoshi/slick-joda-mapper/issues/34","id":147890972,"user":{"login":"blast-hardcheese","id":278900,"avatar_url":"https://avatars.githubusercontent.com/u/278900?v=3","gravatar_id":"","url":"https://api.github.com/users/blast-hardcheese","html_url":"https://github.com/blast-hardcheese","followers_url":"https://api.github.com/users/blast-hardcheese/followers","following_url":"https://api.github.com/users/blast-hardcheese/following{/other_user}","gists_url":"https://api.github.com/users/blast-hardcheese/gists{/gist_id}","starred_url":"https://api.github.com/users/blast-hardcheese/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/blast-hardcheese/subscriptions","organizations_url":"https://api.github.com/users/blast-hardcheese/orgs","repos_url":"https://api.github.com/users/blast-hardcheese/repos","events_url":"https://api.github.com/users/blast-hardcheese/events{/privacy}","received_events_url":"https://api.github.com/users/blast-hardcheese/received_events","type":"User","site_admin":false},"created_at":"2015-10-14T00:26:14Z","updated_at":"2015-10-14T00:26:14Z","body":"@tototoshi: Do you have any recommendations for how to get timezone support in `slick-joda-mapper`? Do you have any ideas on how you would like it to work? We're currently using my branch, because `slick-joda-mapper/master` currently doesn't handle this. I'm very interested in getting this feature supported!"}}