mysql - Hibernate @CreationTimestamp @UpdateTimestamp 在不同的时区

Question

使用以下两个来存储更新和创建时间。

@CreationTimestamp
private Timestamp creationTime;

@UpdateTimestamp
private Timestamp updationTime;

当我创建新记录时，这两个记录都存储在UTC时区中。

+------------+----------------------------------+--------------+--------------+--------------------+--------------------+-----------------------+---------------------------+------+-------------------+---------------------+------------+------------+---------------------+---------------------+----------+
| meeting_id | analysis_description             | meeting_date | preread_date | first_version_date | reminder_sent_date | review_addressed_date | review_comments_addressed | tfls | first_record_flag | current_record_flag | created_by | updated_by | creation_time       | updation_time       | end_time |
+------------+----------------------------------+--------------+--------------+--------------------+--------------------+-----------------------+---------------------------+------+-------------------+---------------------+------------+------------+---------------------+---------------------+----------+
|          4 | BAF312A2122 - CRT 1 - LEGACY CRT | NULL         | NULL         | NULL               | NULL               | NULL                  | NULL                      |    0 | Y                 | Y                   | NULL       | NULL       | 2019-10-04 06:36:36 | 2019-10-04 06:36:36 | NULL     |
+------------+----------------------------------+--------------+--------------+--------------------+--------------------+-----------------------+---------------------------+------+-------------------+---------------------+------------+------------+---------------------+---------------------+----------+
1 row in set (0.00 sec)  

但是，如果我更新记录，创建时间戳会更改为我的本地时间戳。

+------------+----------------------------------+--------------+--------------+--------------------+--------------------+-----------------------+---------------------------+------+-------------------+---------------------+------------+------------+---------------------+---------------------+----------+
| meeting_id | analysis_description             | meeting_date | preread_date | first_version_date | reminder_sent_date | review_addressed_date | review_comments_addressed | tfls | first_record_flag | current_record_flag | created_by | updated_by | creation_time       | updation_time       | end_time |
+------------+----------------------------------+--------------+--------------+--------------------+--------------------+-----------------------+---------------------------+------+-------------------+---------------------+------------+------------+---------------------+---------------------+----------+
|          4 | BAF312A2122 - CRT 1 - LEGACY CRT | 2019-09-30   | 2019-09-30   | 2019-09-30         | 2019-09-30         | 2019-09-30            | yes                       |    1 | Y                 | Y                   | NULL       | NULL       | 2019-10-04 12:08:20 | 2019-10-04 06:38:20 | NULL     |
+------------+----------------------------------+--------------+--------------+--------------------+--------------------+-----------------------+---------------------------+------+-------------------+---------------------+------------+------------+---------------------+---------------------+----------+
1 row in set (0.00 sec)

两者都得到更新，而不仅仅是更新时间。

我尝试了各种配置更改，但它们的行为都相同。

1. spring.jpa.properties.hibernate.jdbc.time_zone=UTC

2. 使用后期构造

   @SpringBootApplication
   public class IdotApplication {
   
   @PostConstruct
   void started() {
       TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
   }
   
   public static void main(String[] args) {
       SpringApplication.run(IdotApplication.class, args);
   }
   

   }

3. 日期而不是时区

   @CreationTimestamp 私有日期创建时间；

4. @CreationTimestamp @Temporal(TemporalType.TIMESTAMP) 私有日期创建时间；

5. @Column(updatable=false) @CreationTimestamp 私有日期创建时间；

也serverTimezone=UTC已经与useLegacyDatetimeCode=false

spring.datasource.url=jdbc:mysql://localhost:3306/rmcdb?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

标签： mysqlhibernatespring-boot

Answer 1

mysql 5.7+该问题是由于 mysql 的一个特性引起的，我正在使用liquibase原始 sql 来生成模式。

如果我在 mysql 中创建一个列，timestamp它默认自动更新。

mysql> create table test ( a timestamp );
Query OK, 0 rows affected (0.02 sec)

mysql> describe test;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| a     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

mysql>

创建新记录时，两个时间戳都在UTC时区中，因为hibernate它覆盖了 mysql。

但是，当记录on update current_timestamp从mysql火灾中更新时hibernate，不会做任何事情，因为它@creationTimeStamp 因此保留在mysql已放置的本地时区中。

为了解决，我将字段定义为

creation_time timestamp null,
updation_time timestamp null

现在mysql不干扰，更新等都只通过休眠完成。

如果您发现解释有点令人困惑并且正在使用mysql下面的文章来澄清。 https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

希望它可以帮助某人。