Handling MySQL datetimes and timestamps in Java

BalusC gave a good description about the problem but it lacks a good end to end code that users can pick and test it for themselves.

Best practice is to always store date-time in UTC timezone in DB. Sql timestamp type does not have timezone info.

When writing datetime value to sql db

    //Convert the time into UTC and build Timestamp object.
    Timestamp ts = Timestamp.valueOf(LocalDateTime.now(ZoneId.of("UTC")));
    //use setTimestamp on preparedstatement
    preparedStatement.setTimestamp(1, ts);

When reading the value back from DB into java,

1. Read it as it is in java.sql.Timestamp type.
2. Decorate the DateTime value as time in UTC timezone using atZone method in LocalDateTime class.

3. Then, change it to your desired timezone. Here I am changing it to Toronto timezone.

   ResultSet resultSet = preparedStatement.executeQuery();
   resultSet.next();
   Timestamp timestamp = resultSet.getTimestamp(1);
   ZonedDateTime timeInUTC = timestamp.toLocalDateTime().atZone(ZoneId.of("UTC"));
   LocalDateTime timeInToronto = LocalDateTime.ofInstant(timeInUTC.toInstant(), ZoneId.of("America/Toronto"));