mysql的日期、时间存储类型的特点及区别

最新推荐文章于 2024-09-23 23:49:25 发布

原创最新推荐文章于 2024-09-23 23:49:25 发布 · 4.5k 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

mysql

本文介绍了Mysql中用于存储日期和时间的几种类型，包括date、time、datetime、timestamp和year。通过比较它们的存储空间、精确度和特殊性质，特别是datetime与timestamp的区别，例如timestamp在存储和检索时会根据时区转换。对于datetime，其支持的范围更广泛，而timestamp则与UTC时间相关联。了解这些差异对于数据库设计至关重要。

Mysql数据库中存储日期时间的类型包括：date、time、datetime、timestamp和year
MySQL允许time、datetime、timestamp类型的值有小数秒，最多到微秒精度

简单粗暴数据库建表查看各个类型的区别

数据库设计，以及各个类型添加数据时的日历框
在这里插入图片描述

数据库中数据

在这里插入图片描述

日期/时间类型所占空间：

在这里插入图片描述

精确度

fsp是小数秒的精度，fsp的值，如果指定的话，必须在0~6范围内，0表示没有小数秒，如果不指定fsp，默认为0。

mysql> create table t_fsp (t time(3),dt datetime(6));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_fsp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| t     | time(3)     | YES  |     | NULL    |       |
| dt    | datetime(6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t_fsp values(now(),now());
Query OK, 1 row affected (0.01 sec)

#t列有3位小数，dt列有6位小数
mysql> select * from t_fsp;
+--------------+----------------------------+
| t            | dt                         |
+--------------+----------------------------+
| 19:45:04.000 | 2018-11-01 19:45:04.000000 |
+--------------+----------------------------+
1 row in set (0.00 sec)

datetime和timestamp的区别

两者都是日期+时间的格式，区别有

datetime [(fsp)]
日期(date)和时间(time)的组合类型。支持的范围是： ‘1000-01-01 00:00:00.000000’\～’9999-12-31 23:59:59.999999’。MySQL用’YYYY-MM-DD HH:MM:SS[.fraction]’
timestamp [(fsp)]
时间戳类型，表示的范围为：‘1970-01-01 00:00:01.000000’ UTC 到 ‘2038-01-19 03:14:07.999999’ UTC。时间戳的值存储的时候是存储从’1970-01-01 00:00:00’ UTC纪元开始的秒数。一个时间戳不能表示’1970-01-01 00:00:00’因为它等于距离纪元0秒，然而0被用来表示’0000-00-00 00:00:00’，即时间戳类型的零值。
fsp和datetime的含义一样。自动初始化和自动更新的定义也和datetime一样。

注意：
timestamp和datetime最大的区别是：datetime在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而timestamp值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区，即查询时，根据当前时区的不同，显示的时间值是不同的。

参考资料：
https://blog.csdn.net/lzufeng/article/details/83628384

标签

#mysql