Mysql日期
MySQL中的时间类型有三大类:日期(Date)、时间(Time)和年(Year)
类型 | 格式 | 范围 | 零值 | 空间大小(MySQL 8.0) |
---|---|---|---|---|
YEAR | ‘YYYY’ | 1901 to 2155 | 0000 | 1字节 |
DATE | ‘YYYY-MM-DD’ | '1000-01-01' to '9999-12-31' | ‘0000-00-00’ | 3字节 |
TIME | ‘hh:mm:ss’ | '-838:59:59' to '838:59:59' | ‘00:00:00’ | 3字节 |
TIMESTAMP | ‘YYYY-MM-DD hh:mm:ss’ | '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC | ‘0000-00-00 00:00:00’ | 4字节 |
DATETIME | ‘YYYY-MM-DD hh:mm:ss’ | '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' | ‘0000-00-00 00:00:00’ | 5字节 |
类型 | MySQL 5.6.4之前 | MySQL 5.6.4及之后 |
---|---|---|
YEAR | 1字节,小端序 | 1字节,小端序 |
DATE | 3字节,小端序 | 3字节,小端序 |
TIME | 3字节,小端序 | 3字节+小数秒存储,大端序 |
TIMESTAMP | 4字节,小端序 | 4字节+小数秒存储,大端序 |
DATETIME | 8字节,小端序 | 5字节+小数秒存储,大端序 |
字符串
这是初学者很容易犯的错误,容易直接将字段设置为 VARCHAR 类型,存储"2021-01-01 00:00:00"这样的字符串。当然这样做的优点是比较简单,上手快。
但是极力不推荐这样做,因为这样做有两个比较大的问题:
- 字符串占用的空间大
- 这样存储的字段比较效率太低,只能逐个字符比较,无法使用 MySQL 提供的日期API
DATETIME
DATETIME 在数据库中存储的形式为:YYYY-MM-DD HH:MM:SS,固定占用 8 个字节
从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。例如,DATETIME(6) 表示可以存储 6 位的毫秒值。
TIMESTAMP
TIMESTAMP 实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中,由于类型 TIMESTAMP 占用 4 个字节,因此其存储的时间上限只能到‘2038-01-19 03:14:07’。
从 MySQL 5.6 版本开始,类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是,若带有毫秒时,类型 TIMESTAMP 占用 7 个字节,而 DATETIME 无论是否存储毫秒信息,都占用 8 个字节。
类型 TIMESTAMP 最大的优点是可以带有时区属性
,因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区,那么类型 TIMESTAMP 是一种不错的选择。
比如新闻类的业务,通常用户想知道这篇新闻发布时对应的自己国家时间,那么 TIMESTAMP 是一种选择。 Timestamp 类型字段的值会随着服务器时区的变化而变化,自动换算成相应的时间,说简单点就是在不同时区,查询到同一个条记录此字段的值会不一样。
当MySQL服务器启动的时候,会查找系统环境来决定时区;
- 可以使用–default-time-zone来指定时区;
- 客户端连接时,默认使用服务器的时区,当客户端与服务器处于同一个时区的时候没什么问题,但是当时区不同时,应该显式设置连接的时区;
- 客户端传递给服务器的TIMESTAMP值,服务器会先转换成UTC时间戳存储;当客户端查询时,再转换成客户端时区的值;
- 其它时间相关的类型没有时区信息。
自动更新
TIMESTAMP和DATETIME可以设置自动初始化与更新:
CREATE TABLE t1 (
id int,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
这样,如果新增记录时没有指定ts和dt的值,那它们默认就是当前时间。
更新记录的时候,也会更新为当前时间。
一般来说我们会创建两个时间字段,一个用于记录创建时间,一个用于记录更新时间:
CREATE TABLE t1 (
id int,
ctime DATETIME DEFAULT CURRENT_TIMESTAMP,
utime DATETIME DEFAULT CURRENT_TIMESTAMP `ON UPDATE CURRENT_TIMESTAMP`
);
TIMESTAMP 的性能问题
TIMESTAMP 还存在潜在的性能问题。
虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多,这并不会带来直接的性能问题。但是如果使用默认的操作系统时区
,则每次通过时区计算时间时,要调用操作系统底层系统函数 __tz_convert()
,而这个函数需要额外的加锁
操作,以确保这时操作系统时区没有修改。所以,当大规模并发访问时,由于热点资源竞争,会产生两个问题:
-
性能不如 DATETIME:DATETIME 不存在时区转化问题。
-
性能抖动:海量并发时,存在性能抖动问题。
为了优化 TIMESTAMP 的使用,建议使用
显式
的时区,而不是操作系统时区。比如在配置文件中显示地设置时区,而不要使用系统时区:
[mysqld]
time_zone = “+08:00”
简单总结一下这两种数据类型的优缺点:
- DATETIME 没有存储的时间上限,而TIMESTAMP存储的时间上限只能到‘2038-01-19 03:14:07’
- DATETIME 不带时区属性,需要前端或者服务端处理,但是仅从数据库保存数据和读取数据而言,性能更好
- TIMESTAMP 带有时区属性,但是每次需要通过时区计算时间,并发访问时会有性能问题
- 存储 DATETIME 比 TIMESTAMEP 多占用一部分空间
数值型时间戳(INT)
我们也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。
这种存储方式的具有 Timestamp 类型的所具有一些优点,并且使用它的进行日期排序以及对比等操作的效率会更高,跨系统也很方便,毕竟只是存放的数值。
缺点也很明显,就是数据的可读性太差了,你无法直观的看到具体时间。
如果需要查看某个时间段内的数据
select * from t where created_at > UNIX_TIMESTAMP(‘2021-01-01 00:00:00’);
总结
- 支持时间的类型有:TIME、DATETIME和TIMESTAMP;
- 支持日期的类型有:DATE、DATETIME和TIMESTAMP;
- 支持小数秒的类型有:TIME、DATETIME和TIMESTAMP;
- 特殊的类型:YEAR;
- MySQL 8.0不支持两位的YEAR类型;
- 小数秒的精度可选值是0-6,默认是0,3代表毫秒,6代表微秒,而2代表10毫秒(如0.11就是110毫秒);
- 存储TIMESTAMP值时会将时间从当前时区转换成UTC时间,返回时再转换回当前时区;
- 默认情况下连接的时区就是服务器的时区,当然每个连接也可以设置自己的时区;
- TIME类型还可以用来表示时间间隔;
- 合法但是超过范围的TIME值会保存为最近的边界值,比如-850:00:00保存为-838:59:59;
- TIMESTAMP有2038问题;
- TIMESTAMP和DATETIME都可以设置自动插入时间与更新时间;
- 使用频率最高的是DATETIME和TIMESTAMP。
常见用法
获取当前时间
- CURDATE(), CURTIME(), NOW():可以获取客户端所在时区的当前时间;
- UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP():可以获得当前的UTC时间;
- CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP():就是CURDATE(), CURTIME(), NOW()的别名
时间的解析
函数 | 返回值 |
---|---|
YEAR() | 日期的年份 |
MONTH() | 月份的数字,从1到12 |
MONTHNAME() | 月份名字,January到December |
DAYOFMONTH() | 日期的数字,从1到31 |
DAYNAME() | 星期的名字,Sunday到Saturday |
DAYOFWEEK() | 星期的数字,周日开始,范围是1到7 |
WEEKDAY() | 星期的数字,周一开始,范围是0到6 |
DAYOFYEAR() | 日期在一整年中的数字,1到366 |
HOUR() | 小时数,0到23 |
MINUTE() | 分钟数,0到59 |
SECOND() | 秒数,0到59 |
MICROSECOND() | 微秒数,0到999999 |
-----------------------后续更新
COND() | 秒数,0到59 | |
MICROSECOND()` | 微秒数,0到999999 |