MySQL 日期数据类型 - date, datetime, timestamp区别及相互转换

数据库时间日期常见存储类型，取出三者后，到后端或者前端均是字符串类型[其实在数据表中存储也是字符类型，不过是有固定格式约束的字符罢了]， 也均可以使用int类型存储，使用代码二次转换.

存储数据到数据表时，选定字段类型， 如何选取合适的时间类型是必要的，

已经确定时间类型，需要转换为合适的字段类型也是在所难免。

如：

设计数据表时， 设计日期时间类型。

在已有数据表，转换日期时间类型。

在数据库中一直有这三个时间类型有点搞不太清楚，总结如下：

[定义]  // 顾名思义 多数名称还是靠谱的, 顾名思义不是我们的错, 如果出现错误, 多数是因为命名的人考虑不周或者就是脑子一时进水.

date: 日期   如：2019-10-26 不带时分秒
datetime: 日期时间   如：2019-10-26 10:53:00 带时分秒 
timestamp: 时间戳，见百度百科： 时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。

[时间范围]

date --> '1000-01-01' to '9999-12-31'.
datetime --> '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
timestamp --> '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

备注：

UTC， Coordinated Universal  Time，协调世界时，又称世界统一时间、世界标准时间、国际协调时间。

由于英文(CUT)和法文(TUC)的缩写不同，作为妥协，简称UTC。

常用日期函数以及转换[MySQL中]
1、获取当前日期：

 CURRENT_DATE() 或者 CURDATE()

2、获取当前时间:

CURRENT_TIME() 或者 CURTIME()

3、获取当前日期和时间

CURRENT_TIMESTAMP() 或者 NOW()

实验SQL:

SELECT CURRENT_TIMESTAMP();
SELECT NOW();

4、Linux/Unix时间戳 和 MySQL时间日期类型之间的转换：

UNIX_TIMESTAMP(NOW())                   // 将mysql的datetime转换成linux/unix的时间戳；日期时间
UNIX_TIMESTAMP(DATE(NOW()))       // 将mysql的date转换成linux/unix的日期。
UNIX_TIMESTAMP(TIME(NOW()))        // 将mysql的time转换成linux/unix的时间。(用问题)
FROM_UNIXTIME(time_t)                      // 将unix的时间戳转换成mysql的datetime；日期时间
DATE(FROM_UNIXTIME(time_t))          // 日期
TIME(FROM_UNIXTIME(time_t))           // 时间

补充：

1. 验证： 到后端或者前端均是字符串类型

测试一：

代码 // 后端

postman测试接口 // 前端

测试二：

代码 // 后端

postman测试接口 // 前端

2. php 框架[kohana，已经衍生为其他框架]中，数据类型的对应转换。

其中： 包含 SQL-92 / SQL:1999 / SQL:2003 / SQL:2008 的标准

后续补充

...

1.数据表中字段类型MySQL是如何区分？他们的本质是什么？

TBD

2.执行如下SQL：// 建表sql: GitHub - cystanford/sql_heros_data: SQL课程-王者荣耀heros数据表

SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros 
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手')) 
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC

结果:

使用DATE函数的疑问：
Q. 对于日期的比较，不是可以直接进行比较吗？ 对于上面的例子，直接可以使用 birthdate 字段进行时间比较，为什么还要使用DATE函数转换一下呢？

A. birthdate字段可能会有时间包含在里面，如2019-01-01 00:00:00，如果直接和2019-01-01比较是会失败的，用DATE函数可以提取出原始数据的日期部分, 将字段 birthdate 转化为日期类型再进行比较

Q. 过滤上线时间 DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'，是MySQL里date类型可以直接与字符串进行比较运算？那这里birthdate可以不用 DATE 函数转换了；Oracle中日期的比较就比较严格，TO_DATE、TO_CHAR 效率也不同.

A. 是的，在ORACLE中可以使用TO_CHAR, TO_DATE做转换：

TO_CHAR 把日期或数字转换为字符串
TO_DATE 把字符串转换为日期类型

同时在MySQL中也有类似的函数

DATE_FORMAT(date,'%Y-%m-%d') 对应 TO_CHAR
STR_TO_DATE(date,'%Y-%m-%d') 对应 TO_DATE

比如:

SELECT DATE_FORMAT(NOW(),'%m-%d-%Y') AS result

SELECT STR_TO_DATE('2017-01-01 00:10:10','%Y-%m-%d %H:%i:%s') AS result

3. 踩坑记录

设置字段类型为datetime后，插入数据一直报错，该数据是从网上搜索得到的当前时区的当前的时间，-- 即东八区时间，最后发现mysql默认是utc时间，格林威治时间，但是似乎这并不是应该是插入失败的原因。

TBD

 总之，当时为了能插入数据，就使用的是mysql 当前时区的时间。