Java OffsetDateTime <-> MYSQL Timestamp issues

Question

I'm having some troubles dealing with OffsetDateTime conversion to and from MYSQL Timestamp. My JVM timezone is GMT+1, my database timezone is UTC, I can confirm it is because now() returns timestamps 1 hour in the past.

Here's my code:

public class TestDBTime {
    
    public static void main(String[] args) throws SQLException, InterruptedException{
        testOffsetDateTime();
    }
    
    private static void testOffsetDateTime() throws SQLException {
        Connection connection = DriverManager.getConnection(DB_Connection.url, DB_Connection.user, DB_Connection.password);

        Instant instant = Instant.now();
        OffsetDateTime offsetDateTime = OffsetDateTime.ofInstant(instant, ZoneId.systemDefault());
        System.out.println("Default offset date time from instant: " +offsetDateTime.toString());
        
        OffsetDateTime offsetDateTimeUTC = instant.atOffset(ZoneOffset.UTC);
        System.out.println("UTC.... offset date time from instant: " +offsetDateTimeUTC.toString());
        
        String update = "update schedule set timestamp = ? where id = 1";
        try (PreparedStatement preparedStatement = connection.prepareStatement(update)){
            preparedStatement.setObject(1, offsetDateTimeUTC);
            preparedStatement.executeUpdate();
        }
        
        String query = "SELECT s.*, now() as now from schedule s where id = 1";
        try (PreparedStatement preparedStatement = connection.prepareStatement(query)){
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            
            OffsetDateTime retrievedDateTime = resultSet.getObject("timestamp", OffsetDateTime.class);
            System.out.println("Offset date time retrieved: " +retrievedDateTime);
            System.out.println("Retrieved odt is equal to original UTC odt: " +retrievedDateTime.equals(offsetDateTimeUTC));
            
            OffsetDateTime retrievedNow = resultSet.getObject("now", OffsetDateTime.class);
            System.out.println("Now: " +retrievedNow);
            
            Timestamp timestamp = resultSet.getTimestamp("timestamp");
            System.out.println("Retrieved timestamp instant is equal to original instant: " +timestamp.toInstant().equals(instant));
        }
    }
}

I execute the code and my console output is:

Default offset date time from instant: 2021-03-26T17:05:11.856+01:00
UTC.... offset date time from instant: 2021-03-26T16:05:11.856Z
Offset date time retrieved: 2021-03-26T17:05:11.856+01:00
Retrieved odt is equal to original UTC odt: false
Now: 2021-03-26T16:05:12+01:00
Retrieved timestamp instant is equal to original instant: true

Here's the problem, the timestamp in the database is saved in my local time, not UTC as I can test with a query in the MYSQL workbench.
Another bizzarre issue is that the now() value, that you can see being 1 hour in the past as it should be since the db is UTC, is retrieved as a +01:00 timestamp, while it should be UTC.
Finally if I retrieve the timestamp using the java Timestamp class (which I know shouldn't be used) reports the instant to be the exact same instant I declared at the beginning of the method.

Java version is 8, mysql connector is 8.0.23 and mysql version is 5.7

The url is "jdbc:mysql://domain.mysql.database.azure.com:3306/schema?useSSL=true&requireSSL=false" — Dario, Mar 26 '21 at 16:21
That helped as the UTC odt is being stored as an UTC timestamp in the db and now() is also being retrieved as a UTC odt. But I can't help but feel this a band-aid/hacky solution. The db already has the UTC timezone property, why do I have to also explicitly specify it in the url? — Dario, Mar 26 '21 at 16:39
you mean this [variable](https://dev.mysql.com/doc/refman/8.0/en/timezone-problems.html) is set for your server? If that was set then it should have worked, otherwise it would pick system time zone. — code_mechanic, Mar 26 '21 at 16:43
TBH, I struggled a bit with this too sometime back and only this thing worked for us, also it is mentioned as connector/j 8.0 limitiation [here](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitations.html) — code_mechanic, Mar 26 '21 at 16:48
In the azure portal the time_zone variable was set to SYSTEM that I assume is UTC since the doc says that's the default timezone. I tried removing `serverTimezone=UTC` from the url while also explicitly setting time_zone to +00:00 in the portal but the problem remains. So I'll have to keep that thing in the url from now on. The link you posted doesn't have anything to do with the issue though, since the reported problem in the link only affects DST switch day which is march 28th in my country — Dario, Mar 26 '21 at 16:55

Arvind Kumar Avinash · Answer 1 · 2021-03-26T17:08:39.517

Your comparison is not correct. 2021-03-26T16:05:11.856Z and 2021-03-26T17:05:11.856+01:00 represent the same instant. You should compare either the Instants or the OffsetDateTimes with the same Instant e.g.

import java.time.OffsetDateTime;
import java.time.ZoneOffset;

public class Main {
    public static void main(String[] args) {
        OffsetDateTime offsetDateTimeUTC = OffsetDateTime.parse("2021-03-26T16:05:11.856Z");
        OffsetDateTime retrievedDateTime = OffsetDateTime.parse("2021-03-26T17:05:11.856+01:00");
        System.out.println(retrievedDateTime.withOffsetSameInstant(ZoneOffset.UTC).equals(offsetDateTimeUTC));
    }
}

Output:

true

Check the documentation for more details on it. To avoid getting a different date-time, you should set serverTimezone=UTC as described in this answer.

I wasn't trying to test if they had the same underlying instant, my point was showing that the two objects had different time zones and hours. Because my problem is that the UTC odt is being stored as gmt+1 on the db — Dario, Mar 26 '21 at 16:36

Java OffsetDateTime <-> MYSQL Timestamp issues

1 Answers1