Search code examples
javamysqlspring-boottestcontainersspring-data-jdbc

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

Solution

  • 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!