MySQL 时区

忘是亡心i 2023-06-28 08:45 30阅读 0赞

1.查看当前时间：
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-01-08 03:20:05 |
    +---------------------+
    
    2.查看时区
    mysql> show variables like '%zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | SYSTEM |
    +------------------+--------+
    time_zone说明MySQL使用system的时区，system_time_zone说明system使用UTC时区
    
    3.修改 time_zone
    修改全局 time_zone
    mysql> set global time_zone = '+8:00';
    Query OK, 0 rows affected (0.00 sec)
    
    修改当前会话 time_zone
    mysql> set time_zone = '+8:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | +08:00 |
    +------------------+--------+
    2 rows in set (0.00 sec)
    
    4.查看当前时间
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-01-08 11:28:08 |
    +---------------------+
    1 row in set (0.00 sec)
    
    5. timestamp 类型与时区有关
    create table user_zone (
      id int(20) NOT NULL AUTO_INCREMENT,
      created_at datetime NOT NULL,
      birthday timestamp NOT NULL,
      PRIMARY KEY (`id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-01-08 11:35:57 |
    +---------------------+
    
    insert into user_zone(created_at, birthday) values(now(), now());
    
    mysql> select * from user_zone;
    +----+---------------------+---------------------+
    | id | created_at          | birthday            |
    +----+---------------------+---------------------+
    |  1 | 2020-01-08 11:36:42 | 2020-01-08 11:36:42 |
    +----+---------------------+---------------------+
    
    set time_zone = 'UTC';
    
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-01-08 03:37:46 |
    +---------------------+
    
    mysql> select * from user_zone;
    +----+---------------------+---------------------+
    | id | created_at          | birthday            |
    +----+---------------------+---------------------+
    |  1 | 2020-01-08 11:36:42 | 2020-01-08 03:36:42 |
    +----+---------------------+---------------------+
    
    ## java 8 时间类 与 数据库映射
    
    @ToString
    @Setter
    @Entity
    @Table(name = "time_type")
    @NoArgsConstructor
    public class TimeType {
    
        private Long id;
        private Instant time;
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        public Long getId() {
            return id;
        }
    
        @Convert(converter = InstantPersistenceConverter.class)
        @Column(name = "time", columnDefinition = "DATETIME")
        public Instant getTime() {
            return time;
        }
    }
    
    import javax.persistence.AttributeConverter;
    import javax.persistence.Converter;
    import java.time.Instant;
    import java.util.Date;
    
    @Converter(autoApply = true)
    public class InstantPersistenceConverter implements AttributeConverter<Instant, Date> {
    
        @Override
        public Date convertToDatabaseColumn(Instant instant) {
            return java.util.Date.from(instant);
        }
    
        @Override
        public Instant convertToEntityAttribute(Date date) {
            return date.toInstant();
        }
    }
    
    mysql> show create table time_type;
    
    | time_type | CREATE TABLE `time_type` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
    
    mysql> select * from time_type;
    +----+---------------------+
    | id | time                |
    +----+---------------------+
    |  1 | 2020-01-08 18:56:41 |
    +----+---------------------+
    
    spring-data-jpa 有 Jsr310JpaConverters 进行转化