timestamp 、 datetime 和unix timestamp傻傻分不清?

构建数据库写程序常需处理日期和时间,MySQL有多种日期时间字段可选,如timestamp、datetime和unix timestamp。文章分析了它们的特点和优缺点,指出timestamp适合记录经常变化的近期时间,能自动处理时区;datetime适合记录固定时间;而unix timestamp使用不太方便。选择应根据业务需求而定。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

构建数据库写程序避免不了使用日期和时间,对于数据库来说,有多种日期时间字段可供选择,如 timestampdatetime 以及使用 int 来存储 unix timestamp((unix timestamp)unix时间戳是从1970年1月1日(UTC/GMT的午夜)开始到现在所经过的秒数)。

经常会有人用字符串存储日期型的数据 (不正确的做法)

缺点 1:无法用日期函数进行计算和比较

缺点 2:用字符串存储日期要占用更多的空间

不仅新手,包括一些有经验的程序员还是比较迷茫,究竟我该用哪种类型来存储日期时间呢?

那我们就一步一步来分析他们的特点,这样我们根据自己的需求选择合适的字段类型来存储 (优点和缺点是比较出来的, 跟父母从小喜欢拿邻居小孩子跟自己比一样的)

datetime 和 timestamp

  • datetime 更像日历上面的时间和你手表的时间的结合,就是指具体某个时间。
  • timestamp 更适合来记录时间,比如我在东八区时间现在是 2016-08-02 10:35:52, 你在日本(东九区此时时间为 2016-08-02 11:35:52),我和你在聊天,数据库记录了时间,取出来之后,对于我来说时间是 2016-08-02 10:35:52,对于日本的你来说就是 2016-08-02 11:35:52。所以就不用考虑时区的计算了。
  • 时间范围是 timestamp 硬伤(1970-2038),当然 datetime (1000-9999)也记录不了刘备什么时候出生(161 年)。

timestamp 和 UNIX timestamp

  • timestamp显示直观,出问题了便于排错,比好多很长的 int 数字好看多了
  • UNIX timestamp是从 1970 年开始累加的,但是 int 支持的范围是 1901-12-13 到 2038-01-19 03:14:07,如果需要更大的范围需要设置为 bigInt。但是这个时间不包含毫秒,如果需要毫秒,还需要定义为浮点数。datetime 和 timestamp 原生自带 6 位的微秒。
  • timestamp 是自带时区转换的,同上面的第 2 项。
  • 用户前端输入的时间一般都是日期类型,如果存储 int 还需要存前取后处理

总结

  • timestamp 记录经常变化的更新 / 创建 / 发布 / 日志时间 / 购买时间 / 登录时间 / 注册时间等,并且是近来的时间,够用,时区自动处理,比如说做海外购或者业务可能拓展到海外
  • datetime 记录固定时间如服务器执行计划任务时间 / 健身锻炼计划时间等,在任何时区都是需要一个固定的时间要做某个事情。超出 timestamp 的时间,如果需要时区必须记得时区处理
  • UNIX timestamps 使用起来并不是很方便,至于说比较取范围什么的,timestamp 和 datetime 都能干。
  • 如果你不考虑时区,或者有自己一套的时区方案,随意了,喜欢哪个上哪个了
  • laravel 是国际化设计的框架,为了程序员方便、符合数据库设计标准,所以 created_at updated_at 使用了 timestamp 是无可厚非的。
  • 有没有一个时间类型即解决了范围、时区的问题?这是不可能的,不是还有 tinyInt BigInt 吗?取自己所需,并且 MySQL 是允许数据库字段变更的。
  • 生日可以使用多个字段来存储,比如 year/month/day,这样就可以很方便的找到某天过生日的用户 (User::where([‘month’ => 8, ‘day’ => 12])->get())

构建项目的时候需要认真思考一下,自己的业务场景究竟用哪种更适合。选哪个?需求来定。

### MySQL Timestamp Datetime 类型的区别及用法 #### 数据存储方式 `TIMESTAMP` `DATETIME` 都用于表示日期时间,但在内部存储上有所不同。`TIMESTAMP` 存储的是 Unix 时间戳,即从 1970 年 1月 1日 UTC 到当前时刻所经过的秒数;而 `DATETIME` 是按照 "YYYY-MM-DD HH:MM:SS" 的格式来保存具体的年份、月份、日子以及小时分钟秒钟等信息[^1]。 #### 范围大小 - **Timestamp**: 支持的有效范围是从 '1970-01-01 00:00:01' UTC 至 '2038-01-19 03:14:07' UTC。 - **Datetime**: 可以表达更广泛的时间区间,默认情况下支持从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'[ ^1 ]. #### 自动更新特性 当表结构定义时指定了属性 `ON UPDATE CURRENT_TIMESTAMP`, 对于 `TIMESTAMP` 字段来说,在记录被更改的时候会自动设置为最新的系统时间;但是这个行为对于 `DATETIME` 不适用,除非显式赋值否则不会改变原有数据中的时间戳[ ^1 ]. #### 占用空间 由于 `TIMESTAMP` 使用较少字节(通常4个字节),相比之下 `DATETIME` 所需的空间更多一些(通常是8个字节)。这使得前者更加节省磁盘资源但也限定了其能表示的时间跨度较小[ ^1]. ```sql -- 创建包含两种类型的测试表格 CREATE TABLE test_time ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 默认创建时间更新时间为现在 dt DATETIME ); ``` #### 时区处理 另一个重要区别在于如何对待不同的服务器或客户端所在的地理位置所带来的影响。每当读取或者写入带有 `TIMESTAMP` 型别的列的数据时都会考虑当前会话设定好的时区参数,并将其转换成协调世界时 (UTC),而在检索这些数值出来显示给用户之前再转回本地时间。然而,`DATETIME` 总是以固定的格式呈现而不受任何时差的影响[ ^1 ].
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

T-OPEN

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值