MySQL 时区-蒲公英云

MySQL 时区

忘是亡心i 2023-06-28 08:45 109阅读 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 进行转化