MySQL 关于表时间的设计以及边界处理

数据类型	范围	时间精度
date	'1000-01-01' to '9999-12-31'	只有日期部分，没有时间部分
datetime	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'	时间格式为 YYYY-MM-DD hh:mm:ss，默认精确到秒
timestamp	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'UTC	同上，默认精确到秒

 

 

 

 

 

 

 

datetime和timestamp 区别

（1） 时间范围不一样，TIMESTAMP 要小很多 ，且最大范围为2038-01-19 03:14:07.999999，到期也不远了。

（2）对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

between and

针对以上三种数据类型边界问题处理

先说结论：datetime和timestamp类型的字段进行between and查询时是不包括右边界的，date类型的包含右边界。

建表如下：

mysql> SELECT * FROM time_type_demo;
+----+---------------------+------------+---------------------+
| id | time_datetime       | time_date  | time_timestamp      |
+----+---------------------+------------+---------------------+
|  1 | 2020-08-10 23:09:29 | 2020-08-10 | 2020-08-10 23:09:45 |
|  2 | 2020-08-17 23:09:58 | 2020-08-17 | 2020-08-17 23:10:06 |
+----+---------------------+------------+---------------------+
2 rows in set

SELECT id,time_datetime FROM time_type_demo WHERE time_datetime BETWEEN '2020-08-10' AND '2020-08-17'

SELECT id,time_date FROM time_type_demo WHERE time_date BETWEEN '2020-08-10' AND '2020-08-17'

SELECT id,time_timestamp FROM time_type_demo WHERE time_timestamp BETWEEN '2020-08-10' AND '2020-08-17'

为什么会产生这样的问题？

因为MySQL对日期的查询是默认“00:00:00”,所以上面的区间实际为：2020-08-10 00:00:00——2020-08-17 00:00:00，2020-08-17 23:09:58是不在这个区间内的，所以查询不到。（严格来说不是不包括右边界，而是右边界为 00:00:00，一般时间都不在这个范围内，如果有时间为00:00:00那也包括。）

而date类型的字段是没有时分秒的，所以date类型是包括右边界的。

另外：not between and 是不包括双边界的

ps：------------------------------------------------------------

问题描述：

为什么mysql在插入时间的时候会莫名增加一秒呢？

原因：

mysql（有些版本的）会对插入的时间的毫秒值大于500的进位操作，所以在此地设置毫秒值为0

public static Date getDateWithMaxTime(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        calendar.set(Calendar.MILLISECOND,0);   //增加毫秒设置为0
        return calendar.getTime();
}