Notice
I wrote this article and was originally published on Qiita on 30 August 2022.
Description
In MySQL specification the type TIME is not affected by time zone setting. But value of TIME is changed when write to database if following conditions match:
- default time zone of MySQL server is different from the time zone of Java application
- using JPA, the field of the entity is LocalTime and the corresponding column is TIME type
- using mysql-connector-java driver version 8.0.22 (I don't sure which version of driver is in affect also)
Bug replication
Set the following parameter in "my.ini".
default-time-zone = "+00:00"
Create table "timetest" in database "test"
CREATE TABLE `timetest` (
`id` int(11) NOT NULL,
`rtime` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `timetest`
ADD PRIMARY KEY (`id`);
ALTER TABLE `timetest`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;
Then the following is the source code.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.2</version>
<relativePath />
</parent>
<groupId>info.saladlam.example</groupId>
<artifactId>mysql-timezone</artifactId>
<version>0.1</version>
<name>mysql-timezone</name>
<description>Test time zone problem of MySQL JDBC driver</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>javax.persistence-api</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
debug=true
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
package info.saladlam.example.spring.test;
import javax.persistence.*;
import java.time.LocalTime;
@Entity
@Table(name = "timetest")
public class TestEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private int id;
@Column(name = "rtime")
private LocalTime time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public LocalTime getTime() {
return time;
}
public void setTime(LocalTime time) {
this.time = time;
}
}
package info.saladlam.example.spring.test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceContextType;
import java.time.LocalTime;
@SpringBootApplication
public class TimeTest implements CommandLineRunner {
private static Logger LOGGER = LoggerFactory.getLogger(TimeTest.class);
private JdbcTemplate jdbcTemplate;
private TransactionTemplate transactionTemplate;
@PersistenceContext(type = PersistenceContextType.TRANSACTION)
private EntityManager em;
@Autowired
public TimeTest(PlatformTransactionManager platformTransactionManager, JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
this.transactionTemplate = new TransactionTemplate(platformTransactionManager);
}
public static void main(String[] args) {
SpringApplication.run(TimeTest.class, args);
}
@Override
public void run(String... args) throws Exception {
// clear entry
transactionTemplate.execute(transactionStatus -> {
jdbcTemplate.execute("USE `test`;");
jdbcTemplate.execute("DELETE FROM `timetest` WHERE `id` = 1;");
jdbcTemplate.execute("ALTER TABLE `timetest` AUTO_INCREMENT = 1;");
return null;
});
// insert by JPA
transactionTemplate.execute(transactionStatus -> {
TestEntity e = new TestEntity();
e.setTime(LocalTime.of(14, 0));
em.persist(e);
return null;
});
// using SQL to retrieve
transactionTemplate.execute(transactionStatus -> {
LOGGER.info("global time zone: {}", jdbcTemplate.queryForObject("SELECT @@global.time_zone;", String.class));
LOGGER.info("session time zone: {}", jdbcTemplate.queryForObject("SELECT @@session.time_zone;", String.class));
jdbcTemplate.execute("SET @@session.time_zone = '+01:00';");
LOGGER.info("session time zone: {}", jdbcTemplate.queryForObject("SELECT @@session.time_zone;", String.class));
jdbcTemplate.execute("USE `test`;");
LOGGER.info("time: {}", jdbcTemplate.queryForObject("SELECT `rtime` FROM `timetest` WHERE `id` = 1;", String.class));
return null;
});
}
}
Logging
2022-08-30 20:41:38.758 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL statement [USE `test`;]
2022-08-30 20:41:38.774 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL statement [DELETE FROM `timetest` WHERE `id` = 1;]
2022-08-30 20:41:38.775 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL statement [ALTER TABLE `timetest` AUTO_INCREMENT = 1;]
2022-08-30 20:41:38.811 DEBUG 14716 --- [ main] org.hibernate.SQL : insert into timetest (rtime) values (?)
2022-08-30 20:41:38.864 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [SELECT @@global.time_zone;]
2022-08-30 20:41:38.876 INFO 14716 --- [ main] i.saladlam.example.spring.test.TimeTest : global time zone: +00:00
2022-08-30 20:41:38.876 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [SELECT @@session.time_zone;]
2022-08-30 20:41:38.881 INFO 14716 --- [ main] i.saladlam.example.spring.test.TimeTest : session time zone: +00:00
2022-08-30 20:41:38.881 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL statement [SET @@session.time_zone = '+01:00';]
2022-08-30 20:41:38.883 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [SELECT @@session.time_zone;]
2022-08-30 20:41:38.885 INFO 14716 --- [ main] i.saladlam.example.spring.test.TimeTest : session time zone: +01:00
2022-08-30 20:41:38.885 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL statement [USE `test`;]
2022-08-30 20:41:38.886 DEBUG 14716 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [SELECT `rtime` FROM `timetest` WHERE `id` = 1;]
2022-08-30 20:41:38.889 INFO 14716 --- [ main] i.saladlam.example.spring.test.TimeTest : time: 13:00:00
2022-08-30 20:41:38.898 INFO 14716 --- [extShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-08-30 20:41:38.902 INFO 14716 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2022-08-30 20:41:38.933 INFO 14716 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Time value "14:00" is written to database, but actual value "13:00" is saved.
Bug fix
This bug was fixed on driver version 8.0.30. (may be fixed on the earlier driver, but I don't try it.)
Top comments (0)