前言
在MySQL中时间类型的选择有很多,比如:date、time、year、datetime、timestamp...
在某些情况下还会使用整形int、bigint来存储时间戳
根据节省空间的原则,当只需要存储年份、日期、时间时,可以使用year、date、time
如果需要详细的时间,可以选择datetime、timestamp或者使用整形来存储时间戳
以下是不同类型的格式、时间范围、占用空间相关信息
类型 | 格式 | 范围 | 空间(字节Byte) |
---|---|---|---|
date | YYYY-MM-DD |
1000-01-01 to9999-12-31 |
3 |
time | hh:mm:ss .fraction |
-838:59:59.000000 to 838:59:59.000000 |
3 |
year | YYYY |
1901 to 2155 |
1 |
datetime | YYYY-MM-DD hh:mm:ss [.fraction ] |
1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.499999 |
8 |
timestamp | 存储为时间戳,显示为YYYY-MM-DD hh:mm:ss |
1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.499999 UTC |
4 |
int | 时间戳 | 4 | |
bigint | 时间戳 | 8 |
本篇文章主要概述datetime、timestamp与整形时间戳相关的内容,并在千万级别的数据量中测试它们的性能,最后总结出它们的特点与使用场景
datetime
datetime不仅可以存储日期、时间,还可以存储小数点后续的毫秒等 YYYY-MM-DD hh:mm:ss
[.fraction
]
比如datetime(3) 就可以保留三位小数 2023-04-22 20:47:32.000
当datetime不保留小数时使用5 Byte,需要保留小数时多加3 Byte,总共8 Byte (5.6.X之后)
datetime是最常用的时间类型,在存储、读取的性能和数据库可视化方面都不错,但它只能展示固定的时间,如果在不同时区,看到的时间依旧是固定的,不会随着时间变化
timestamp 时间戳
MySQL中的timestamp能有效的解决时区问题
timestamp用于存储时间戳,在进行存储时会先将时间戳转换为UTC
UTC是世界统一时间,比如我们的时区为东八区,则是在UTC的基础上增加八小时
时间戳在进行存储时,先根据当前时区转换成UTC,再转换成int类型进行存储
时间戳在进行读取时,先将int类型转换为UTC,再转换为当前时区
当前时区指的是MySQL服务端本地时区,默认为系统时区,可以进行配置
当前时区发生变化时,读取时间戳会发生变化
比如我的服务端默认系统为东八区(+8:00),当我修改为(+11:00)
[mysqld]
default_time_zone = +11:00
读取时,所有的timestamp都增加3小时
如果MySQL时区设置为系统时区(time_zone = SYSTEM)时,进行时区转换会调用系统函数,高并发下开销会很大
@Resource
private JdbcTemplate jdbcTemplate;
@Test
/**
* 10个线程每次查询10次 一次查500条
* timestamp:11,978ms
* datetime:9,057ms
*/
void getTimestamp() throws BrokenBarrierException, InterruptedException {
String timestampSql = "select SQL_NO_CACHE test_timestamp from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp = '2022-10-10 00:00:00' and test_datetime