mysql数据库中的时间类型及相关操作

一、MySQL中 日期和时间类型

参考 MySQL 数据类型（还有其他知识）

参考 SQL 数据类型

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

MySQL中 日期和时间类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

二、日期和时间类型的使用

开始使用的是 Mysql 5.5.47

SELECT VERSION();

创建表时出错了，遇到两个问题。。

问题一：

[Err] 1067 - Invalid default value for 'create_time'

查阅资料，原来是版本问题

在5.5到5.6.4版本里，对于DEFAULT CURRENT_TIMESTAMP 子句，只能TIMESTAMP类型列上指定。

而从5.6.5开始以后的版本，对于DEFAULT CURRENT_TIMESTAMP子句可以指定到TIMESTAMP或者DATETIME类型列上。

问题二：

想给一个类型为date字段设置一个default value（CURRENT_DATE，CURRENT_DATE()，NOW()）但试了很久都不行。最后试了下 DEFAULT NULL 成功了，再试下 DEFAULT 1也成功了。最后网上查了一下资料，原因是date类型的default value 一定要是一个常量，不能是一个函数或者是表达式。所以不能给MySQL的date类型的列设置默认值。

三、TIMESTAMP和DATETIME比较

相同点：

两者都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期。

不同点：

他们的的存储方式，大小(字节)，表示的范围不同。

TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

DATETIME，不做任何改变，基本上是原样输入和输出。

总结：TIMESTAMP和DATETIME 都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期， 除了存储方式和存储范围以及大小不一样，没有太大区别。但对于跨时区的业务，TIMESTAMP更为合适。

四、时间与时间戳之间转换

有些应用生成的时间戳是比这个多出三位，是毫秒表示，如果要转换，需要先将最后三位去掉（标准的10位数字，如果是13位的话可以以除以1000的方式），否则返回NULL

#将时间转换为时间戳unix_timestamp
SELECT UNIX_TIMESTAMP('2019-02-22 13:25:07'); #1550813107
 
#将时间戳转换为时间from_unixtime
SELECT FROM_UNIXTIME(1550813107); #2019-02-22 13:25:07
 
#NOW
SELECT UNIX_TIMESTAMP(NOW()); #1550813420
SELECT FROM_UNIXTIME(1550813420); #2019-02-22 13:30:20

 

参考 MySQL的10位或13位时间戳获取，表示及13位时间戳的存储

五、按时间日期查询

然后我将 Mysql 改为 Mysql 5.7.17，升一下版本

参考 MySQL DATE_FORMAT() 函数

#MySQL 按时间日期查询
DROP TABLE IF EXISTS tb_test;
CREATE TABLE IF NOT EXISTS tb_test(
  id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  name VARCHAR(100) DEFAULT NULL COMMENT '名称',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT '时间测试表';
 
#插入数据
INSERT INTO tb_test (name) VALUES
('luo'),
('lei'),
('luolei'),
('xing'),
('dxx');
 
#修改数据
UPDATE tb_test SET name='luo1', update_time='2021-01-23' WHERE id=100;
UPDATE tb_test SET name='luo2', update_time='2022-02-23' WHERE id=101;
UPDATE tb_test SET name='luo3', update_time='2023-03-23' WHERE id=102;
 
#根据年月日查数据
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m-%d')='2019-02-22';
#根据年月查数据
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m')='2021-01';
#根据年查数据
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y')='2022';
 
#根据日期区间查询数据,并排序 
SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y') BETWEEN '2019' AND '2023' ORDER BY update_time ASC;

java中时间类型的范围比较

<if test="startTime != null and startTime != ''">
	<![CDATA[AND (DATE_FORMAT(c.birthday, '%Y-%m-%d') <= DATE_FORMAT( #{startTime}, '%Y-%m-%d'))]]>
</if>
<if test="endTime != null and endTime != ''">
	<![CDATA[AND (DATE_FORMAT(c.birthday, '%Y-%m-%d') <= DATE_FORMAT( #{endTime}, '%Y-%m-%d'))]]>
</if>
<if test="search == null or search == ''">
	AND (DATE_FORMAT(a.register_date, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d')OR DATE_FORMAT(a.update_date, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d'))
</if><!—系统当前时间的判断-->