时间戳(timestamp)和 datetime的区别

本文比较了数据库中的TIMESTAMP和DATETIME数据类型,讨论了它们的存储范围、时区处理、自动更新行为和存储空间。重点提到了TIMESTAMP的2038年问题以及它们在不同场景下的适用性。

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

今天在设计数据库表的时候需要给created_time updated_time 分配数据存储类型

想到存时间戳(timestamp)和 datetime的区别 。

问了一下chat:

请问 timestamp这个从1970年开始的时间戳 和正常使用的 datetime有什么区别吗

在关系数据库中,TIMESTAMPDATETIME 是表示日期和时间的数据类型,它们在功能上有一些区别:

  1. 范围和存储方式

    • DATETIME 数据类型存储范围是 '1000-01-01 00:00:00''9999-12-31 23:59:59'占用 8 字节的存储空间。                                                        
    • TIMESTAMP 数据类型存储范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC,占用 4 字节的存储空间。这个范围受限于 32 位整数的最大值,因此存在所谓的“2038年问题”。

                注:UTC格林威治时间(1)↓↓↓↓↓

  1. 时区处理

    • DATETIME 不会考虑时区,它是存储特定日期和时间的数据类型。
    • TIMESTAMP 存储的是相对于 UTC(协调世界时)的时间戳,并在存储时会将其转换为 UTC,读取时也会根据当前时区进行相应的转换。
  2. 自动更新行为

    • 在 MySQL 中,TIMESTAMP 类型的列可以设置为默认值为 CURRENT_TIMESTAMP,这样当一行数据被创建或更新时,这个字段会自动记录当前的时间戳。而 DATETIME 类型的列需要在插入或更新数据时手动指定值。
  3. 存储空间

    • TIMESTAMP 占用的存储空间更小,因为它只存储了一个时间戳,而不是完整的日期和时间信息。

总的来说,TIMESTAMP 更适合用于记录与时间相关的操作,尤其是需要考虑时区的情况,而 DATETIME 更适合用于存储特定的日期和时间信息,不涉及时区转换。

        为什么会有2038年危机 。

在一般的电脑中,int占用4字节,32比特,数据范围为-2147483648~2147483647[-2^31~2^31-1]

无符号整型(unsigned int )占4个字节,范围 0 ~ 4294967295

(1):在19世纪末和20世纪初,国际上为了解决全球航海和铁路运输中出现的时间标准问题,选择了格林威治天文台的子午线作为参考点。1884年,格林威治子午线被选定为国际时间的基准线,也就是世界时(UTC)的起点。这条子午线穿过伦敦郊区的格林威治天文台。

协调世界时(UTC)是世界上的标准时间。它是基于原子钟的时间,被视为全球的时间标准,不受任何地区或国家的影响。UTC 不受太阳时间、地球自转等因素影响,是一种由国际原子时(TAI)通过精确的原子钟测量得出的时间标准。

        为什么是1970年?        

因为Unix 操作系统 在1969年开始被开发,在1971年正式被发表,所以最初规定的时间戳是从 1971 年1月1日 00:00:00开始,但是后来为了方便计算就取了个整数,就改到了1970年

### Oracle 数据库中的 Timestamp 使用方法 在 Oracle 数据库中,`TIMESTAMP` 是一种用于存储日期时间的数据类型。它不仅能够保存年月日的信息,还可以精确到秒甚至更精细的时间单位(如纳秒)。这使得 `TIMESTAMP` 类型非常适合需要高精度计时的应用场景。 #### 创建带有 Timestamp 的表 当创建一张新表并希望其中某一列能记录非常精准的时间戳时,可以定义该列为 `TIMESTAMP` 或者带精度的 `TIMESTAMP(p)` 形式: ```sql CREATE TABLE example_table ( id NUMBER PRIMARY KEY, event_time TIMESTAMP(6), -- 定义event_time字段为最高可达微秒级别的Timestamp description VARCHAR2(100) ); ``` #### 插入包含 Timestamp 值的数据 向上述表格插入一条含有具体时刻的新纪录可以通过如下方式实现: ```sql INSERT INTO example_table (id, event_time, description) VALUES (1, TO_TIMESTAMP('2023-08-15 14:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF'), 'An important event'); ``` 这里使用了函数 `TO_TIMESTAMP()` 将字符串转换成相应的 `TIMESTAMP` 值;注意格式模型 `'YYYY-MM-DD HH24:MI:SS.FF'` 中包含了小数部分 `.FF` 来表示分数秒。 #### 查询特定时间范围内的记录 为了检索发生在某个时间段内所有的事件,可利用标准 SQL 比较运算符来筛选符合条件的结果集: ```sql SELECT * FROM example_table WHERE event_time BETWEEN TO_TIMESTAMP('2023-08-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2023-08-15 15:00:00', 'YYYY-MM-DD HH24:MI:SS'); ``` 此命令会返回所有发生于指定小时区间 `[14:00 ~ 15:00]` 内的所有条目[^1]。 #### Flashback Table 到过去某一时点 除了常规操作外,Oracle 还提供了强大的闪回特性之一——Flashback Table 功能,允许用户将整个表恢复至之前的状态。下面是一个例子展示了如何把名为 `hr.temp_employees` 表的内容还原到给定的时间戳位置: ```sql FLASHBACK TABLE hr.temp_employees TO TIMESTAMP TO_TIMESTAMP('2013-10-17 09:30:00', 'YYYY-MM-DD HH24:MI:SS'); ``` 这条语句将会使 `hr.temp_employees` 表回到 2013 年 10 月 17 日上午九点半的样子。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值