面试: DATETIME和TIMESAMP的对比
尽量使用timesamp
相同点
- 两个数据类型存储时间格式一致,均为 YYYY-MM-DD HH:MM:SS
- 两个数据类型都包含[日期和时间]
- 两个数据类型都可以存储微秒的小数秒(秒后6位小数秒)
不同点
- 使用的存储空间不同
- timesamp使用4个字节的存储空间
- datetime使用8个字节的存储空间
- 存储时间范围不同
timesamp只使用datetime一半的存储空间,因此存储的时间范围更小
- 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’。
- 与时区的关系
- timesamp会根据时区变化,具有特殊的自动更新能力。
- 如果存入的是null,timesamp会自动存储为当前时间,datetime不会自动存储当前时间,会直接存入NULL值
常用场景
在什么场景中,使用 DATETIME 或 TIMESTAMP 更合适?
TIMESTAMP 使用场景:计算飞机飞行时间
一架飞机,从中国北京起飞,降落在美国纽约,计算它从北京飞往纽约的飞行时间。飞机在北京时间 2021-10-10 11:05:00 从北京起飞,在纽约时间 2021-10-10 09:50:00 降落(JL8006)。
这个场景中,如果使用 TIMESTAMP 来存时间,起飞和降落时间的值,都会被转换成 UTC 时间,所以它们直接相减即可获得结果。但如果使用 DATATIME 格式存时间,还需要进行转换,才可以完成,容易出错。
DATATIME 使用场景:记录信息修改时间
如果只是记录文件修改时间,最后更新时间这种不涉及加减转换的情况,用 DATATIME 来存更直接,更方便,可读性高,不绕弯子,不容易出错。
实践
- create table time_test (tstmp timestamp not null, dttime datetime not null);
- insert into time_test(dttime) values(‘20181212001122’);
mysql> create table test_time(a timestamp, b datetime);
mysql> insert into test_time values (now(), now());
mysql> select * from test_time;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2019-01-12 21:58:54 | 2019-01-12 21:58:54 |
+---------------------+---------------------+
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
mysql> set time_zone='+00:00';
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +00:00 |
+-------------+
mysql> select * from test_time;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2019-01-13 05:58:54 | 2019-01-12 21:58:54 |
+---------------------+---------------------+
unix时间戳与日期的转换
1、FROM_UNIXTIME()
作用:unix时间戳转换为日期
2、UNIX_TIMESTAMP
作用:日期转换为时间戳
参考:https://blog.csdn.net/rocling/article/details/82695068