MySQL的时间类型有哪些？有哪些时间函数？如何使用？

MySQL时间类型

 mysql有多种时间类型，他们之间的区别是什么？使用时又需要注意什么呢？

1.MySQL的5种时间类型

如下表所示：

类型	大小	格式	0值意义
DATE	4 bytes	YYYY-MM-DD	0000-00-00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIME	3 bytes	HH-MM-SS	00-00-00
YEAR	1 bytes	YYYY	0000

• DATETIME与TIMESTAMP的区别？
  • timestamp是4字节，表示时间范围小，时间范围从1970-01-01 00:00:01到2038-01-19 03:14:07，存储的是从1970-01-01 00:00:00到特定时间的秒数，所以同一个时间戳 在不同时区的人看来表示的时间是不同的。
  • datetime是8个字节，存储的就是时间的数字，范围从1000-01-01 00:00:00到9999-12-31 23:59:59，可以表达的时间范围比较大，由于是直接存的时间数值，所以不同时区的人看到的是相同的时间。

2.常用的日期函数与比较方式

• 获取当前时间

#获取当前日期时间
SELECT NOW()
SELECT CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP()
SELECT LOCALTIME()
SELECT LOCALTIME
SELECT LOCALTIMESTAMP
SELECT LOCALTIMESTAMP()

#获取当前日期
SELECT CURDATE()
SELECT CURRENT_DATE
SELECT CURRENT_DATE()

#获取当前时间
SELECT CURRENT_TIME
SELECT CURRENT_TIME()
SELECT CURTIME()

• 时间的加减

#日期的加减
SELECT DATE_ADD('2020-05-14 20:12:37',INTERVAL '1 2' YEAR_MONTH)
SELECT ADDDATE('2020-05-14 20:12:37',INTERVAL '1 2' YEAR_MONTH)
SELECT SUBDATE('2020-05-14 20:12:37',INTERVAL '1 2' YEAR_MONTH)
SELECT DATE_SUB('2020-05-14 20:12:37',INTERVAL '1 2' YEAR_MONTH)

INTERVAL 是间隔关键字，后面跟unit，下图为单位

• DATEDIFF(date1,date2)

  • date1减去date2,返回天数，参数可以是日期类型也可以是时间类型
  • SELECT DATEDIFF('2020-05-14 20:12:37','2019-05-14 20:12:37')
  • 结果：366

• DATE_FORMAT(date,format)

  • 时间格式转换函数，可以将时间格式转换为任意格式
  • SELECT DATE_FORMAT('2020-05-14 20:12:37','%Y-%m时间转换')
  • 结果为：2020-05时间转换
  • format中可以使用获取date中时间的参数，常用的如下：

转换参数	单位	获取的位数	范围
%Y	年	4
%y	年	2
%M	月		月的英文名如’May’
%m	月	2	[01,12]
%D	天		英文简写如’1st’‘3rd’
%d	天	2	[01,31]
%H	时	2	[00,24]
%h	时	2	[00,12]
%i	分	2	[00,59]
%S或%s	秒	2	[00,59]
%p	上下午		PM/AM
%r	上下午格式时间		'12:12:37 AM’格式时间
%W	周		英文周名如’Thursday’
%w	周	1	[0,6]周日-周六
%j	返回今天是今年的第几天	3	[001,366]

3.使用方式

• 在选择一段范围比如一个月时，我们可以直接用between，如：求创建时间在5月的所有数据

SELECT * FROM table WHERE create_time BETWEEN '2020-05-01' AND '2020-05-31'