Mysql数据库的时间(1)一(时间类型)

深藏阁楼爱情的钟 2024-03-27 16:57 13阅读 0赞

本文基于MySQL 5.7版本。

<table> 
 <tbody> 
 <tr> 
 <td></td> 
 <td>DATE</td> 
 <td style="width:140px;">TIME</td> 
 <td style="width:140px;">DATETIME</td> 
 <td style="width:169px;">TIMESTAMP</td> 
 <td>YEAR</td> 
 </tr> 
 <tr> 
 <td>占字节数</td> 
 <td>3</td> 
 <td style="width:140px;">3</td> 
 <td style="width:140px;">8</td> 
 <td style="width:169px;">4</td> 
 <td>1</td> 
 </tr> 
 <tr> 
 <td>取值范围</td> 
 <td> '1000-01-01' 到 '9999-12-31' </td> 
 <td style="width:140px;"> '-838:59:59.000000' 到 '838:59:59.000000' </td> 
 <td style="width:140px;"> '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999' </td> 
 <td style="width:169px;"> '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC </td> 
 <td> 1901 到 2155 或者0000 </td> 
 </tr> 
 <tr> 
 <td>小数位精度</td> 
 <td>0</td> 
 <td style="width:140px;">0~6</td> 
 <td style="width:140px;">0~6</td> 
 <td style="width:169px;">0~6</td> 
 <td>0</td> 
 </tr> 
 <tr> 
 <td>显示格式</td> 
 <td>YYYY-MM-DD或YY-MM-DD</td> 
 <td style="width:140px;">hh:mm:ss[.fraction]</td> 
 <td style="width:140px;"> YYYY-MM-DD hh:mm:ss[.fraction] 或 YY-MM-DD hh:mm:ss[.fraction] </td> 
 <td style="width:169px;"> YYYY-MM-DD hh:mm:ss[.fraction] 或 YY-MM-DD hh:mm:ss[.fraction] </td> 
 <td>YYYY</td> 
 </tr> 
 <tr> 
 <td>赋值</td> 
 <td>允许使用符合格式的字符串或数字赋值</td> 
 <td style="width:140px;">允许使用符合格式的字符串或数字赋值</td> 
 <td style="width:140px;">允许使用符合格式的字符串或数字赋值</td> 
 <td style="width:169px;">允许使用符合格式的字符串或数字赋值</td> 
 <td>允许使用符合格式的字符串或数字赋值</td> 
 </tr> 
 <tr> 
 <td>小数位分隔符</td> 
 <td>无</td> 
 <td style="width:140px;">只识别小数点.</td> 
 <td style="width:140px;">只识别小数点.</td> 
 <td style="width:169px;">只识别小数点.</td> 
 <td>无</td> 
 </tr> 
 <tr> 
 <td>日期分隔符</td> 
 <td>任何标点</td> 
 <td style="width:140px;">无</td> 
 <td style="width:140px;">任何标点</td> 
 <td style="width:169px;">任何标点</td> 
 <td>无</td> 
 </tr> 
 <tr> 
 <td>时间分隔符</td> 
 <td>无</td> 
 <td style="width:140px;">任何标点</td> 
 <td style="width:140px;">任何标点</td> 
 <td style="width:169px;">任何标点</td> 
 <td>无</td> 
 </tr> 
 <tr> 
 <td>无效值处理（strict mode关闭状态）</td> 
 <td> 转化为 0000-00-00 </td> 
 <td style="width:140px;"> 转化为 00:00:00 </td> 
 <td style="width:140px;"> 转化为 0000-00-00 00:00:00 </td> 
 <td style="width:169px;"> 转换为 0000-00-00 00:00:00 </td> 
 <td>转换为0000</td> 
 </tr> 
 </tbody> 
</table>

MySQL中表示时间值的日期和时间数据类型有以下五个：

（1）DATE

（2）TIME

（3）[DATETIME][]

（4）[TIMESTAMP][]

（5）YEAR

### 日期和时间数据类型语法 ###

MySQL允许对TIME、DATETIME和TIMESTAMP使用小数秒，精度最高可达微秒（6位小数）。要定义包含小数秒日期和时间数据类型的列，使用

type\_name(fsp)，

其中type\_name是TIME、DATETIME或TIMESTAMP，而fsp是小数秒精度，fsp的值，如果给定，必须在0 ~ 6之间。值为0表示没有小数部分。如果省略，默认精度为0。

例如:

CREATE TABLE date_time_test (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

### Date ###

支持的范围 '1000-01-01' to '9999-12-31'，MySQL以'YYYY-MM-DD'格式显示Date类型，但允许使用字符串或数字将值赋给DATE列。字面常量转换规则如下：

（1）格式为'YYYY-MM-DD'或'YY-MM-DD'的字符串。任何标点字符都可以用作日期部分之间的分隔符。例如，'2012-12-31'、'2012/12/31'、'2012^12^31'和'2012@12@31'是等价的。

（2）格式为'YYYYMMDD'或'YYMMDD'的没有分隔符的字符串，只要该字符串具有日期的意义。例如，'20070523'和'070523'被解释为'2007-05-23'，但'071332'是非法的(它有无意义的月和日部分)，会变成'0000-00-00'。

（3）作为YYYYMMDD或YYMMDD格式的数字，只要该数字作为日期有意义。例如，19830905和830905被解释为“1983-09-05”。

（4）以 `YY-MM-DD` 格式或者 `YYMMDD` 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。

### TIME和TIME(fsp) ###

支持的范围'-838:59:59.000000' to '838:59:59.000000'，MySQL以'hh:mm:ss\[.fraction\]'格式显示TIME，但允许使用字符串或数字向TIME列赋值。TIME类型这个时间范围表明它不仅可以表示一天的时间，也可以表示经过的时间或两个事件之间的时间间隔。字面常量转换规则如下：

（1）MySQL将带有冒号的TIME值缩写为一天中的时间，例如：'11:12'表示'11:12:00'，而不是 '00:11:12'；

（2）MySQL将没有冒号的TIME缩写值最右边的两个数字看成秒，例如：'1112'和1112都表示'00:11:12'

（3）在时间部分和小数秒部分之间识别的唯一分隔符是小数点；

（4）默认情况下，位于TIME范围之外但在其他方面有效的值将被裁剪到该范围中最近的端点。例如，'-850:00:00'和'850:00:00'会转换为'-838:59:59'和'838:59:59'。无效的TIME值将被转换为“00:00:00”。注意，由于'00:00:00'本身是一个有效的TIME值，因此无法从存储在表中的'00:00:00'值判断原始值是'00:00:00'还是无效的。

#### DATETIME和DATETIME(fsp) ####

DATETIME类型是Date和Time的组合。支持的范围从'1000-01-01 00:00:00.000000' 到'9999-12-31 23:59:59.999999'。MySQL以'YYYY-MM-DD hh:mm:ss\[.fraction\]'的格式显示DATETIME类型。但允许使用字符串或数字向DATETIME列赋值。字面常量转换规则如下：

表中的任何TIMESTAMP或DATETIME列都可以具有自动初始化和更新属性。

（1）格式为“YYYY-MM-DD hh:mm:ss”或“YY-MM-DD hh:mm:ss”字符串。任何标点字符都可以用作日期部分或时间部分之间的分隔符。例如，'2012-12-31 11:30:45'、'2012^12^31 11+30+45'、'2012/12/31 11\*30\*45'和'2012@12@31 11^30^45'是等价的。

（2）在日期和时间部分和小数秒部分之间识别的唯一分隔符是小数点。

（3）日期和时间部分可以用T而不是空格分隔。例如，'2012-12-31 11:30:45' '2012-12-31 t11:30:45 '是等价的。

（4）格式为'YYYYMMDDhhmmss'或'YYMMDDhhmmss'无分隔符的字符串，只要该字符串具有日期的意义。例如，'20070523091528'和'070523091528'被解释为'2007-05-23 09:15:28'，但'071122129015'是非法的(它有一个无意义的分钟部分)，会变成'0000-00-00 00:00:00'。

（5）YYYYMMDDhhmmss或YYMMDDhhmmss格式的数字，只要该数字作为日期有意义。例如，19830905132800和830905132800被解释为“1983-09-05 13:28:00”。

（6）以 `YY-MM-DD HH:MM:SS` 格式或者 `YYMMDDHHMMSS` 格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。

（7）在阿里的Java开发手册中，推荐的是用[datetime][]，没说明原因，估计是忌惮timestamp的最大值只能到2038年。然后因为date/time/datetime/year存的是死的字符串,不能根据时区自动转换时间,因此需要我们select出来后在代码中根据时区自己加减时间.

### TIMESTAMP和TIMESTAMP\[(fsp)\] ###

支持的范围'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC（UTC指的是世界标准时间），TIMESTAMP值存储为自纪元('1970-01-01 00:00:00' UTC)以来的秒数，但是它不能表示'1970-01-01 00:00:00'，因为这表示从纪元开始的0秒，值0保留用于表示“0000-00-00 00:00:00”，即“零”的TIMESTAMP值。TIMESTAMP的字面常量同DATETIME。其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。

向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

这个时区需要你自己去mysql服务里修改.然后timestamp会自动转换成你设置的时区的时间.(时区设置及其注意点详见我的SCDN收藏!)

> \#修改当前的时区  
> SET time\_zone = '+9:00';

>  **TIMESTAMP和DATETIME的区别：**
> 
> TIMESTAMP存储空间比较小，表示的日期时间范围也比较小。  
> 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。  
> 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。  
> TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

### **YEAR和YEAR(4)** ###

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节的存储空间。

在MySQL中，YEAR有以下几种存储格式：

以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。  
以2位字符串格式表示YEAR类型，最小值为00，最大值为99。  
当取值为01到69时，表示2001到2069；  
当取值为70到99时，表示1970到1999；  
当取值整数的0或00添加的话，那么是0000年；  
当取值是日期/字符串的’0’添加的话，是2000年。  
从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)，从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

**注意**如果没有启用strict SQL模式，MySQL会将无效的YEAR值转换为0000。在严格SQL模式下，试图插入无效的YEAR值将产生错误。

## 总结 ##

###    1）timestamp ###

按官方的说法，timestamp存储的是UTC时间戳，展示的时候会将时间戳转为当前时区进行展示。

例如有个字段是在东八区的时候插入的，东八区时候select出来是 2021-04-04 21:51:43，如果数据库的时区变成东九区，比如设置会话时区为东九区set time\_zone='+9:00'，则select查出的值是2021-04-04 22:51:43。

要不是因为timestamp的范围过小，最大值只能到2038年，我觉得timestamp是最好的最方便的类型

总结：反正只要知道timestamp存的是时间戳就可以了。

### 2）datetime、date、time、year ###

这些类型的字段，官方也说明白了，就是不会帮你转换。说白了就跟写死的字符串一样，存进去是什么样的值，不管你的时区调成什么样都不会再改变了。

总结：这些类型存的是死值，跟死的字符串一样。

### 3）now()、curtime()、curdate() ###

这些函数的值，是会随着时区的不同而获取到不同的值的。

[DATETIME]: https://so.csdn.net/so/search?q=DATETIME&spm=1001.2101.3001.7020
[TIMESTAMP]: https://so.csdn.net/so/search?q=TIMESTAMP&spm=1001.2101.3001.7020
[datetime]: https://so.csdn.net/so/search?q=datetime&spm=1001.2101.3001.7020