MySQL面试：DATETIME和TIMESAMP的对比&&时间戳与日期的转换

面试： DATETIME和TIMESAMP的对比

尽量使用timesamp

相同点

• 两个数据类型存储时间格式一致，均为 YYYY-MM-DD HH:MM:SS
• 两个数据类型都包含[日期和时间]
• 两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

不同点

1. 使用的存储空间不同

• timesamp使用4个字节的存储空间
• datetime使用8个字节的存储空间

2. 存储时间范围不同
   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’。

3. 与时区的关系

• timesamp会根据时区变化，具有特殊的自动更新能力。

4. 如果存入的是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