浅谈数据库时间类型——以PostgreSQL数据库为例

数据库中的时间格式有哪些？

废话不多说，上货。

追根溯源

生命不息，学习不止，总能发现新玩意。这不今天改bug发现了个有意思的现象，场景是这样的：
网页展示了数据列表，其中包含了时间相关的数据列，数据上显示时间类似2021-10-12 15：22：44.123456，这时候我就有疑问了-
既然时间格式都有哪些，具体都有哪些不同，究竟有哪些作用？

所以就引出了这篇博客。

时间格式列举

PostgreSQL保存日期时间格式的数据类型有多种，主要有：TIME、DATE、TIMESTAMP、INTERVAL。日期时间类型输入像字符串类型输入一样，需要加单引号。每种日期时间类型都有合法的取值范围，超出范围时系统会将“零”插入数据记录中。

1.解释一下第二张图中（）中p参数：
time, timestamp, interval 接受一个可选的精度值 ，这个精度值就是p，以指明秒域中小数部分的位数，p 的范围对 timestamp 和 interval 类型是从 0 到大约 6
1s=1000ms，所以16：42：36.001 才是一毫秒
2.解释一下 time zone
time zone是时区的意思，在数据库可根据 SHOW TIMEZONE；命令查询当前数据库服务器时区.

如果更改时区可通过 SET timezone = ‘时区’; 来设定对应的时区
例如 SET timezone = ‘UTC’;

time详解

time分为TIME without time zone和TIME with time zone两种格式，后者为携带时区的时间格式，在数据库中可使用alert tablename add timename（time）/add timename（timez）添加不带时区/带时区的列。
下面是time的一些例子

datatime详解

datatime是非常常用的时间格式，datatime有四种格式，可通过show/set datestyle 对datatime的时间格式进行修改。


总结：
1、允许为空值，可以自定义值，系统不会自动修改其值。
2、不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据。
3、虽然不可以设定默认值，但是可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。
结论：datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。

timestamp 详解

timestamp 既包含日期也包含时间，以int形式储存，最多可有小数点后六位，包含时区和非时区两种

当选择timestamptz时，数据库会根据设定的时区从新计算你传入的日期值转换成设定时区对应的时间

总结：
timestamp
1、允许为空值，但是不可以自定义值，所以为空值时没有任何意义。
2、默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间。
3、数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间。
4、以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。
结论：timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。

datatime与timestamp 的区别

1、两者的存储方式不一样:
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
而对于DATETIME，不做任何改变，基本上是原样输入和输出。
2、两者所能存储的时间范围不一样:
timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。
datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。
2、存储大小不同
在MySQL5.6.4之前，datetime占8个字节，timestamp占4个字节
现在MySQL规范规定，datetime的非小数部分需要5个字节，而不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度。

日期与时间函数

链接:https://www.runoob.com/postgresql/postgresql-datetime.html

最后给你们推荐一本书吧

如果有错误或者需要补充请写在下面，跟我打一架！

古德拜~