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.
pom.xml
<?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>
application.properties
debug=true
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
TestEntity.java
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;
}
}
TimeTest.java
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.)