[Anorm] Retrieve time in UTC timezone

[Jatin Puri]

In my web 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).

I think on retrieving value from database, it is retrieving it as IST.

My application.conf details:

db.default.driver=com.mysql.jdbc.Driver

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

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

    resultSet.getTimestamp("date", 

         new GregorianCalendar(TimeZone.getTimeZone("UTC")))

This works on the same database.

[Jatin Puri]

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<String>() {

@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

[Jatin Puri]

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. My fault, Thanks.