Question 406903 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Testcontainer Timezone Issue When Using Spring Data JDBC

I have a weird issue that I’m hoping is just something stupid I’m overlooking (and will gladly eat crow on).

  • I have a Spring Boot project that makes use of Spring Data JDBC. It also uses Testcontainer’s MySQL container for testing.

  • I’m running this locally on my MBP.

  • I’m using Java 21, Spring Boot 3.2.4, and Testcontainers 1.19.7.

  • I use java.time.Instant to handle all datetimes in the Spring application.

  • I’m using datetime(6) to store dates and times in the database.

  • I have the MySQL container set to use UTC on startup. I have verified that the MySQL container is running UTC by logging into the MySQL instance and checking @@global.time_zone and even @@session.time_zone just for fun.

  • I have the Spring project set to use a custom clock which always uses UTC, i.e. a @Bean-annotated method is called that simply does return Clock.systemUTC(). I have verified that this line is reached and executed.

  • I have customer converters set and registered to go back and forth from java.time.Instant and java.sql.Timestamp, although it’s interesting to note that the "reading from the database" converter doesn’t seem to work.

When I run the project tests (e.g. via Cucumber), I can see that the dates/times going in to the database are absolutely UTC. However, when I pause things and go to look in the database, it’s actually UTC-0800.

Now, if I set the JVM argument -Duser.timezone=UTC, everything works perfectly, i.e. the datetimes in the MySQL Testcontainer are properly UTC.

I’d rather not have to manually set the JVM argument above for each such test I have to run.

Am I missing something obvious or doing something dumb? Any assistance in this matter would be most appreciated.

Thanks in advance!

ETA: A minimal, reproducible example can be found here: https://github.com/BHSDuncan/spring-data-jdbc-timezone-issue

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up finding the cause of my issue: In a base class for my tests, there was a static block that was calling TimeZone.setDefault() and was setting to UTC, and the Timestamp conversion code relies on the defaultTimeZone property. I probably had it in there as I was trying to figure out the Spring Data JDBC date conversion functionality and figured that setting the clock and default time zone to UTC would be a "belt and suspenders" type of idea. Looks like I was wrong.

    By removing that static block along with the customer converters I had in place, everything seems to work.

    It was discovered that a Timestamp to Instant ReadingConverter is missing and an issue is being created for that in Spring Data Relational.

    Many thanks to Jens for the back-and-forth!


  2. 0

    This was raised as an issue on Spring-Data-Relational with additional information.
    Based on a reproducer I created a test doing some logging:

    The relevant code:

    ExampleModel exampleModel = repository.save(new ExampleModel(Clock.systemUTC()));

        Instant persistedInstant = exampleModel.getDtCreated();

        Object selectedViaJDBC = jdbcTemplate.queryForObject("select dt_created from example_table where id = ?", Object.class, exampleModel.id);
        String instantAsString = jdbcTemplate.queryForObject("select date_format(dt_created,'%H:%i:%s') from example_table where id = ?", String.class, exampleModel.id);

        System.out.println("----------------------------------------------");
        System.out.println("Instant.now(Clock.systemUTC())):t"+ Instant.now(Clock.systemUTC()));
        System.out.println("Instant.now():t" + Instant.now());
        System.out.println("persisted: tt" + persistedInstant);
        System.out.println("selected via JDBC:t" + selectedViaJDBC + " (" + selectedViaJDBC.getClass().getName() + ")");
        System.out.println("selected as String:t"+ instantAsString);
        System.out.println("----------------------------------------------");

    The output is as follows from a test run with local timezone being UTC+2. Local clock says: 16:51

    ----------------------------------------------
Instant.now(Clock.systemUTC())):    2024-09-09T14:51:22.755941Z
Instant.now():  2024-09-09T14:51:22.756070Z
locale offset(h):   1
persisted:      2024-09-09T14:51:22.673594Z
selected via JDBC:  2024-09-09 16:51:23.0 (java.sql.Timestamp)
selected as String: 16:51:23
----------------------------------------------

    The instant is created as 14:51, since it is in a rough sense the time you would see in UTC.
    Before it gets persisted it gets converted to java.sql.Timestamp by interpreting java.sql.Timestamp as a LocalDateTime. Since I’m 2hours ahead of UTC 2 hours get added and I get a timestamp of 16:51, which gets send to the database.

    According to https://dev.mysql.com/blog-archive/support-for-date-time-types-in-connector-j-8-0/ this gets converted to UTC time again and stored in the database. But whenever you retrieve it, it gets converted back to a java.sql.Timestamp in your local time zone again. So still 16:51.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search