Mysql的日期

数据库常用的时间日期

日期类型	存储空间	日期格式	日期范围
time	3 bytes	HH:MM:SS	-838:59:59 ~ 838:59:59
date	3 bytes	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
year	1 bytes	YYYY	YEAR(2):1970-2070，year(4) : 1901-2155
datetime	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	4 bytes	YYYYMMDDHHMMSS	1970-01-01 00:00:00 ~ 2037-12-31 23:59:59
varchar	14+ bytes
int

日期类型字段的设计，影响数据库的查询效率，同时与业务功能的正确性也有很大的关系。

1. 字符串 存储日期的缺点

• 无法使用mysql中提供的日期函数，为mysql很多查询带来不便：

举例：用户表有个字段birthday，表示用户的生日，查询2023年出生的所有用户
如果birthday 是日期类型，可以使用YEAR函数
如果birthday 是字符串类型，无法使用year函数，需要做各种转换处理。

• 占用空间较大：

举例：存时间：2023-01-01 00:00:00

存储类型	需要字节长度
varchar	19个字节
datatime	8个字节
timestamp	4个字节

2. int 类型存时间

存时间戳，存一个int类型的数值，用一个时间戳来表示时间。

• 优点
  用int存时间，需要进行日期排序以及按日期范围查询时，就变成了普通的数字的比较了，查询速度较快。
• 缺点
  致命问题，可读性差。
  特例，20230412 用int方式存，比时间戳可读性高，但是无法用时间函数进行查询，查一段时间范围内的数据需要报时间转换成这种格式，数据查出来还要手动处理才能转换成Date类型数据。

3. datetime VS timestamep 比较

• 占用空间
  官网介绍：
  

在 MySQL5.6.4 是一个分水岭
在 MySQL5.6.4 之前，DATETIME 固定占用 8 个字节。
从 MySQL5.6.4 开始，DATETIME 类型开始支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度，例如，DATETIME(6) 表示可以存储 6 位的毫秒值，那么此时，DATETIME 占用的字节数，就跟后面的毫秒数有关了，如果 DATETIME 没有详细到毫秒，那么占用 5 个字节，如果详细到毫秒了，那就看情况，根据毫秒的精度，占用不同的空间，毫秒精度小于等于 2 时，总共占用 6 个字节；毫秒精度小于等于 4 时，总共占用 7 个字节；毫秒精度小于等于 6 时，总共占用 8 个字节。
同样，由上图我们也可以看出，在 MySQL5.6.4 之前，TIMESTAMEP 固定占用 4 个字节，从 MySQL5.6.4 开始，依据毫秒的精度，TIMESTAMEP 占用的字节数介于 4 到 7 之间。

所以无论是 TIMESTAMEP，还是 DATETIME，都是比字符串节省空间的。

• 存储范围

DATETIME 的存储范围介于 1000-01-01 00:00:00 到 9999-12-31 23:59:59 之间。
TIMESTAMP 的存储范围则介于 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 之间。

很明显 DATETIME 的存储范围要更大一些。

• 性能比较

从毫秒数转换到 TIMESTAMP 并不费事，但是当要进行时区转换的时候，需要调用操作系统底层系统函数，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改，一加锁，效率就低了。
对于这个问题，只存在于 TIMESTAMP 中，因为 DATETIME 不存在时区转化问题。
对于 TIMESTAMP，建议使用显式的时区，而不是操作系统时区。

datetime不存在时区转化的问题，timestamep建议使用显式的时区

• 底层存储

TIMESTAMP 类型最大的优势在于自带时区属性，因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择，TIMESTAMP 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。

举个 TIMESTAMP 的使用场景例子：

新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家时间，那么 TIMESTAMP 是一种不错的选择。
TIMESTAMP 会随着时区的变化而自动调整，而 DATETIME 不会。

时区的问题一定要谨慎，不过时区问题也并非一定要在数据库中解决，也可以在前端或者服务端用代码处理下。