Retrieve time in UTC and not local (Might be anorm bug)

Question

In my application (play 2.2 with anorm), I wish to store all the time information in UTC only. So to do this, I have a below implicit which does the work:

implicit val dateTimeToStatementUTC = new ToStatement[Date] {
      def set(s: java.sql.PreparedStatement, index: Int, aValue: Date): Unit = {
        if (aValue == null) {
          s.setTimestamp(index, null)
        } else {
          s.setTimestamp(index, new java.sql.Timestamp(aValue.getTime()), new GregorianCalendar(TimeZone.getTimeZone("UTC")))
        }
      }
    }

override def createPlaylist(play: CreatePlaylist) = {
    DB.withConnection { implicit conn =>
      SQL("INSERT INTO playlist (userId,date) VALUES ({userId},{date})").on(
        'userId -> play.userId, 'date -> play.date).executeInsert()
    }
  }

The above works! But the Issue is on retrieving I need to get the UTC timestamp and not the local one. When I simply do below it returns timestamp in the Local timezone. :

def getUserPlaylist(userId: Int): Seq[Playlist] = {
    DB.withConnection { implicit conn =>
      SQL("SELECT * from playlist where userId={uid}").on('uid -> userId).as(simple.*)
    }
  }

i.e. if I insert 5:30:00 IST in DB. It gets inserted as 00:00:00. But on retrieving and printing it prints 00:00:00 IST where as it should 5:30:00 IST (Time different between IST and UTC is 5 hour 30 min)

How do I retrieve time as per UTC timezone? In Java I would just do

resultSet.getTimestamp("date", 
     new GregorianCalendar(TimeZone.getTimeZone("UTC")))

Edit: Some more Information. I think it might be a bug.

When I access the same DB with the same config from Spring code,

jdbcTemplate.query("SELECT * from bookmarks where userId = 4", new ResultSetExtractor() {

        @Override
        public String extractData(ResultSet rs) throws SQLException,
                DataAccessException {
            while(rs.next()){
                Timestamp a = rs.getTimestamp("date",Calendar.getInstance(TimeZone.getTimeZone("GMT")));
                SimpleDateFormat d = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
                System.out.println(d.format(a)+" <> "+rs.getObject("date")+" <> "+rs.getTimestamp("date"));
            }
            return null;
        }
    });

It prints: 2013-12-09 05:30:00 IST <> 2013-12-09 05:30:00.0 <> 2013-12-09 05:30:00.0 Where as my play project gave me: 2013-12-09 00:00:00.0

Answer 1

The Issue got resolved. I was using this:

db.default.url="jdbc:mysql://localhost/videos?useLegacyDatetimeCode=false&useUnicode=true&serverTimezone=UTC&"

instead of this:

jdbc:mysql://localhost/videos?useLegacyDatetimeCode=false&useUnicode=true&serverTimezone=UTC

In spring for every use of & we had to append it with amp. This should not be used with play.