数据库时间字段TIMESTAMP和DATETIME区别

TIMESTAMP 和 DATETIME 是 MySQL 中用于存储日期和时间的两种数据类型。它们之间的主要区别在于存储的范围和自动更新的方式。

1. 存储范围：

• DATETIME 存储的范围是 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
• TIMESTAMP 存储的范围是 ‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’，因为它以 Unix 时间戳格式存储，使用 32 位整数表示秒数。

2. 自动更新：

• 对于 DATETIME，你需要手动更新它的值，例如在插入或更新记录时显式指定新的日期和时间。
• 对于 TIMESTAMP，可以配置在记录更新时自动更新为当前时间，使用 ON UPDATE CURRENT_TIMESTAMP 子句。

示例：

查看TIMESTAMP 字段是否为自动更新：

1. 查看表的创建语句：

SHOW CREATE TABLE your_table_name;

将 your_table_name 替换为实际的表名。在结果中找到 TIMESTAMP 字段，看是否有 ON UPDATE CURRENT_TIMESTAMP 的设置。例如：

CREATE TABLE `your_table_name` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `your_timestamp_column` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- 其他字段...
);

下面这个就不是自动更新的

2. 修改 TIMESTAMP 字段为自动更新：

如果 your_timestamp_column 字段没有自动更新设置，你可以使用 ALTER TABLE 语句进行修改：

ALTER TABLE your_table_name
MODIFY COLUMN your_timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

这将把现有的 TIMESTAMP 字段修改为具有自动更新功能的配置。

请注意，如果已经存在数据，修改字段可能需要谨慎处理，因为它可能会影响到现有数据。确保在修改之前备份数据或测试过程中使用样本数据。
更新数据前：

更新数据：

UPDATE employees SET name="jerrey" where id=1001

时间自动更新了！

ps：这里改创建时间是不合适的，应该是更新时间字段才设置自动更新，演示效果到位，懒得改了。

3. 存储空间：

• TIMESTAMP 在存储空间上可能会比 DATETIME 更有效，因为它以整数形式存储秒数。

4. 时区的处理：

• DATETIME 是无时区的，存储的时间是相对于数据库服务器的本地时区。
• TIMESTAMP 可以存储时区信息，根据数据库配置和会话的时区来解释。

示例：

原始时间数据

更改会话时区增加两小时

SET time_zone = '+10:00'

时间数据跟着发生改变

总结

在使用 created_at 和 updated_at 这样的时间戳字段时，通常选择 TIMESTAMP 的原因是其**自动更新功能，使得在记录更新时不需要显式地更新时间戳字段。**这样可以减少代码的复杂性，并确保这些字段总是反映出记录的创建和更新时间。

当然，具体的选择还取决于你的应用需求和对时间精度的要求。如果你的应用对时间范围不受限制，并且需要更大的存储范围，可以选择使用 DATETIME。