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
– Dario, Commented Mar 26, 2021 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
– Dario, Commented Mar 26, 2021 at 16:39
you mean this variable is set for your server? If that was set then it should have worked, otherwise it would pick system time zone. — code_mechanic
– code_mechanic, Commented Mar 26, 2021 at 16:43

Arvind Kumar Avinash · Accepted Answer · 2021-03-26 17:08:39Z

2

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.

edited Mar 26, 2021 at 17:08

answered Mar 26, 2021 at 16:31

Arvind Kumar Avinash

81k10 gold badges98 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dario Over a year ago

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

Collectives™ on Stack Overflow

Java OffsetDateTime <-> MYSQL Timestamp issues

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related