存储的时间表示与标准时间 UTC 的时差, UTC 表示0:0
date: 不带时区, 存储日期和时间, 精确到秒
timestamp: 不带时区, 存储日期和时间, 精确到小数点后9位
timestamp with timezone: 包括时区, 客户端时区和UTC的时区差, 例如: '1999-04-15 8:00:00-8:00' 这里是 负8小时.
timestamp with local timezone: 存储到数据库时, 会发生转换. 存储的是客户端所在时区, 转换: 客户端所在时区->UTC->db所在时区, 所以如果db所在时区设置成UTC, 那么右边就不需要转换.
注意, 这个转换都是先转换成 utc, 然后再从UTC像另一种时间转换, 所以, 如果需要存储 timestamp with local timezong 这种类型, 建议将DB TIME ZONE 设置成 UTC, 这样, 转换的次数可以减少一次.
面对这么多类型, 什么时候使用什么类型 ?
- 如果对时间要求到小数的秒, 则可以选择 TIMESTAMP
- 如果希望数据库能够自动在数据库时区和会话时区之间进行时间转换, 使用TIMESTAMP WITH LOCAL TIME ZONE
- 如果希望跟踪数据入口的会话时区, 使用 timestamp with time zone.
- 我们可以使用 timestamp 类型代替 date类型, 一个不带亚秒精度信息的 timestamp 会占用 7个字节, 和date一样, 如果 timestamp 带有压秒数据, 就会占用 11 个字节的存储空间.
其他一些考虑:
- 要是必须和 timestamp 数据类型出现之前的已有应用程序相兼容, 就的使用 DATE 类型.
- 如果使用数据库是 oracle 9i 以前的版本, 我们别无选择只能用 DATE 类型.
Interval
interval year to month:
interval day to second:
下边是计算工龄的例子
1: -- chap10_02.sql
2: declare
3: start_date TIMESTAMP;
4: end_date TIMESTAMP;
5: service_interval interval year to month;
6: years_of_service number;
7: months_of_service number;
8: begin
9: start_date := to_timestamp('29-dec-1988', 'dd-mon-yyyy');
10: end_date := to_timestamp('26-dec-1995', 'dd-mon-yyyy');
11:
12: -- 确定工龄, 并显示出来:
13: service_interval := (end_date - start_date) year to month;
14: dbms_output.put_line(service_interval);
15:
16: years_of_service := extract(year from service_interval);
17: months_of_service := extract(month from service_interval);
18: dbms_output.put_line(years_of_service || 'years and ' || months_of_service || 'months');
19: end;
20: /
21: show errors;
oracle 分为两大时区
数据库时区 select dbtimezone from dual
session时区 select sessiontimezone from dual
alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI: SS’;
在了解了相关数据类型后，那么我们该如何在它们之间做出选择呢？
当你不需要保存时区/地区信息的时候，选择使用TIMESTAMP数据类型，因为它一般需要7-11bytes的存储空间，可以节省空间。
当你需要保存时区/地区信息的时候，请选择使用TIMESTAMP WITH TIMEZONE数据类型。比如一个跨国银行业务应用系统，需要精确纪录每一笔交易的时间和地点(时区)，在这种情况下就需要纪录时区相关信息。因为需要纪录时区相关信息，所以需要多一些的存储空间，一般需要13bytes。
当你并不关心操作发生的具体地点，而只是关心操作是在你当前时区(当地)几点发生的时候，选择使用TIMESTAMP WITH LOCALTIME ZONE。比如一个全球统一的change controlsystem。用户可能只关心某某操作是在我的时间几点发生的(比如中国用户看到的是北京时间8:00am，而伦敦的用户看到的是0:00am)。记住，此类行不保存时区/地区信息，因此如果需要保存相关信息的要慎重！