我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ,欢迎关注收藏,也欢迎大家转载,但是请在文章开始地方标注文章出处,谢谢!
由于博客中有大量代码,通过页面浏览效果更佳。
本文为个人学习《Expert Oracle Database Architecture Techniques and Solutions for High Performance and Productivity(第四版本》一书过程中的笔记与理解分享,仅用于学习与交流,部分内容参考原书观点并结合>实际经验进行整理。若涉及版权问题,请联系删除或沟通处理。也请大家支持购买原版书籍。
时间戳(TIMESTAMP)类型:比日期更强大的时间管理工具
时间戳(TIMESTAMP)类型和日期(DATE)类型很像,但它更强大——支持小数秒和时区。这对于需要精确时间记录或跨时区的应用非常有用。
1. 基本TIMESTAMP类型
基本TIMESTAMP的语法很简单:
TIMESTAMP(n)
这里的n
是可选的,表示秒的小数部分精度,可以是0到9之间的数字。
- 如果
n=0
,TIMESTAMP的功能和DATE完全一样,连存储方式都相同(占7字节)。 - 如果指定了小数精度(如
n=9
),TIMESTAMP会占用更多空间(11字节)来存储更精确的时间。
举个例子:
存储17-JUL-21 11.46.08.192870000 PM
这个时间戳时,最后的192870000
就是小数秒,它们被保存在额外的4个字节里。
使用时要注意:
对TIMESTAMP进行加减运算时,数据库有时会先把它转成DATE类型,导致丢失小数秒和时区信息。为了保持精度,最好使用专门的INTERVAL
类型来操作时间。
计算时间差:
两个DATE相减,得到的是一个数字(相差的天数)。而两个TIMESTAMP相减,得到的是一个时间间隔(INTERVAL),它可以直接告诉你相差了多少天、小时、分钟、秒和小数秒,非常直观。
如果想要知道相差多少年、月,计算会复杂一些,因为年和月的长度不固定(闰年、不同月份天数不同)。不过,通常当你需要以年、月为单位展示时,秒级的精度已经足够,微秒的损失可以接受。
2. 带时区的TIMESTAMP (TIMESTAMP WITH TIME ZONE)
这个类型在TIMESTAMP基础上增加了时区支持,需要13字节存储空间(多了2字节来保存时区信息)。
它的核心特点是: 存储时间时,会同时记录下当时指定的时区。在查询时,数据库会利用这2字节的时区信息,将时间正确地显示出来。
为什么这很重要?
现在的应用大多是全球性的,用户遍布世界各地。如果没有内置的时区支持,应用程序就需要自己写代码来存储和转换不同时区的时间,既麻烦又容易出错。现在这个工作可以直接交给数据库来处理。
举个例子:
你可以同时存储纽约时间和洛杉矶时间:
INSERT INTO table VALUES (TIMESTAMP'2014-02-27 16:02:32.212 US/Eastern', -- 纽约时间TIMESTAMP'2014-02-27 16:02:32.212 US/Pacific' -- 洛杉矶时间
);
虽然它们看起来都是“16点02分”,但数据库知道这两个时间代表的是不同时刻。如果你计算它们的差值,数据库会先转换成UTC标准时间再计算,正确得到3小时的时差。
3. 本地时区的TIMESTAMP (TIMESTAMP WITH LOCAL TIME ZONE)
这个类型是最省心也是最常用的。它占用的空间和普通TIMESTAMP一样(7或11字节)。
它的特点是: 存入时间时,数据库会自动将其转换并存储为数据库服务器所在时区的时间。查询时,显示的也是数据库本地时区的时间。你不需要关心源时区是什么,数据库帮你处理好了所有的转换。
对比一下三种存储方式:
假设数据库时区是US/Mountain
(美国山区时间),我们存入一个US/Pacific
(美国太平洋时间)下午4点的时间。
- DATE类型:只存下午4点,时区和小数秒信息丢失。
- TIMESTAMP WITH TIME ZONE:存下下午4点(太平洋时间),并将其转换为UTC时间(加8小时)存储,同时保留“太平洋时间”这个时区信息。
- TIMESTAMP WITH LOCAL TIME ZONE:数据库发现输入是太平洋时间下午4点,而自己是山区时间(比太平洋快1小时),于是直接存为山区时间下午5点。
一个重要的注意事项:
一旦你的数据库里创建了TIMESTAMP WITH LOCAL TIME ZONE
类型的字段,数据库的时区就被“锁定”了,不能再修改。因为如果修改了,所有已存储的基于原时区的时间值就全都不准确了,需要全部重新计算,这会导致非常复杂的问题。
总结
类型 | 关键字 | 是否支持小数秒 | 是否支持时区 | 特点与适用场景 |
---|---|---|---|---|
日期 | DATE |
否 | 否 | 基础类型,仅存储到秒,无需时区支持时使用。 |
时间戳 | TIMESTAMP(n) |
是 | 否 | 需要高精度时间(如科学实验、高频交易)但无关时区时使用。 |
带时区的时间戳 | TIMESTAMP(n) WITH TIME ZONE |
是 | 是(记录源时区) | 必须记录事件发生的原始时区时使用(如跨国会议系统、日志审计)。 |
本地时区的时间戳 | TIMESTAMP(n) WITH LOCAL TIME ZONE |
是 | 是(自动转换) | 最常用。需要处理不同时区但统一展示时使用(如全球化的电商订单时间)。注意:一旦使用,数据库时区不可更改。 |
简单来说,对于大多数需要处理时间的应用,TIMESTAMP WITH LOCAL TIME ZONE
通常是最佳选择,它在提供时区支持的同时保持了简便性。
------------------作者介绍-----------------------
姓名:黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)