Date
类型是我们经常使用的时间类型数据表示，包括了年月日时分秒信息。作为
Date
类型的一个拓展，
Oracle
提供了
Timestamp
数据类型，作为高精度时间类型的体现。
1
、
Timestamp
的高精度
Timestamp
在官方中的定义方式是
timestamp(n)
，其中
n
表示秒级片段（
fractional_seconds
）的精确度。作为
Date
类型的一个拓展，
Timestamp
提供了更为精确的时间定位。
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
13-7
月
-11
07.52.16
.
562000
下午
+08:00
SQL> select sysdate from dual;
SYSDATE
------------------------------
2011-7-13 20:13:55
使用
systimestamp
函数，我们可以获取到当前时间的
timestamp with local time zone
取值。默认情况下，我们发现和
sysdate
显示的有一些差异。控制和显示
timestamp
格式的参数，可以通过
v$nls_parameters
进行查看。
SQL> select * from v$nls_parameters;
PARAMETER
VALUE
------------------------------ ----------------------------------------
（篇幅原因，有省略
…
）
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
19 rows selected
参数
nls_timestamp_format
和
nls_timestamp_tz_format
分别来控制
timestamp
和
timestamp with local zone
的显示格式。
上面
systimestamp
显示的信息，可以解析为：
2011-7-13
19:52:16
。稍稍费解的是秒后面的
562000
数字。这个是
Timestamp
的秒片段（
fractional seconds
），就是秒向下的精度划分。
timestamp
定义中的
n
就是指定的每秒划分的精度范围。
实际应用中，
n
取值为
0-9
，默认为
6
。从上面例子上的
systimestamp
函数返回值上看，也是的确如此。
SQL> create table t (d1 timestamp, d2 timestamp(0), d3 timestamp(9));
Table created
SQL> desc t;
Name Type
Nullable Default Comments
---- ------------ -------- ------- --------
D1
TIMESTAMP(6) Y
D2
TIMESTAMP(0) Y
D3
TIMESTAMP(9) Y
那么，
timestamp
类型的高精度有什么作用呢？个人认为大部分还是为了前端并发访问时候的锁机制，还有一些特殊行业的高精度需求。很多开发框架都需要使用一个高精度的数据列来标记行访问的时间顺序，通过
timestamp
的精度，基本可以满足这部分的需要。
2
、
Timestamp with time zone
Timestamp
类型的另一个特性就是时区
timezone
特性。我们知道世界各地所处的时区不同，对应同一个时刻显示的时间是不同的。所以如果精确定义一个时间点，特别是对应一个全球性系统的时候，时区
Timezone
是不可或缺的考虑因素。
Date
类型只能适应于一般时间的表示，如果加入了时区因素，就无能为力了。在这样的情况下，就可以选择
Timestamp
类型的一个拓展类型，
timestamp with time zone
。这样就可以把当前对应的时区信息加入到数据列中。
函数
systimestamp
返回标准类型就是
timestamp with time zone
。与一般
timestamp
类型不同的是，
timestamp with time zone
额外加入了当前时区的信息。
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
13-7
月
-11 07.52.16.562000
下午
+08:00
+08:00
表示时区，全球有
12
个时区。系统的时区通过参数设置或者当前操作系统
OS
的配置来获取。
SQL> create table t (d1 date, d2 timestamp, d3 timestamp with time zone);
Table created
SQL> insert into t values (systimestamp, systimestamp, systimestamp);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t;
D1
D2
D3
-------------------- -----------
2011-7-13 21:12:32
13-7
月
-11 09.12.32.859000
下午
13-7
月
-11 09.12.32.859000
下午
+08:00
加入时区信息之后，可以做到全球时间下的绝对时间比较。
此外，
timestamp
还有一个
timestamp with local time zone
的拓展类型。也包括时区信息，与
time zone
的差别有下面几个：
ü
不管是什么时区的日期插入到数据库中，都会进行正规化工作。将其他时区的日期转化为数据库对应的时区（
Database Time Zone
）中；
ü
当进行检索操作的时候，
local time zone
类型数据又会被转化为当前检索会话对应的
time zone
中。这一系列的转化操作对
end user
而言都是透明的；
在对应会话和数据库的时区上，与系统参数
NLS_TERRITORY
是相关的。
Oracle
往往通过这个参数的国家设置来判断时区。而客户端的参数往往与
nls_lang
相关。
3
、
Timestamp
显示格式控制
最后我们一起来看看
timestamp
显示的格式方式。默认情况下，
timestamp
以及
timestamp with time zone
的显示是与系统参数
nls_timestamp_format
和
nls_timestamp_tz_format
有关。在我们的实验环境下，两个格式字符串（
format string
）分别为：
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
其中，
DD
、
MON
、
HH
和
MI
都是我们非常熟悉的日期格式字符串。
RR
表示使用年的后两位进行表示。而
SSXFF
是一个组合条件，应该被拆分为
SS
、
X
和
FF
三个部分。
SS
表示秒信息，
X
表示基数开关，而
FF
表示小于一秒的时间片段。
X
表示的基数开关是什么意思呢？我们通过实验就可以看出情况。
SQL> select to_char(systimestamp,'DD-MON-RR HH.MI.SSXFF AM') SSXFF, to_char(systimestamp,'DD-MON-RR HH.MI.SSFF AM') SSFF from dual;
SSXFF
SSFF
-------------------------------------- -------------------------------------
13-7
月
-11 09.37.
16
.843000
下午
13-7
月
-11 09.37.
16843000
下午
加入了
X
表示开关之后，就在秒为加入了一个
.
号表示分割。便于查看。
而
AM
表示是否使用（
AM
和
PM
）显示上下午信息，
TZR
则表示是否显示出时区信息。
当显示
timestamp
类型的时候，如果没有明确指定显示格式，系统会自动使用
nls_timestamp_format
系列参数。
SQL> select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss ff tzr') from dual;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
--------------------------------------------------------------
2011-07-13 21:40:50 265000 +08:00
如果期望在会话（或者系统）一级修改参数，就要使用
alter session
或者
alter system
方法。
SQL> select to_char(systimestamp) from dual;
TO_CHAR(SYSTIMESTAMP)
-----------------------------------------------------------------------
13-7
月
-11 09.42.44.343000
下午
+08:00
//
对当前会话修改参数；
SQL> alter session
set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss ff tzr';
Session altered
SQL> select to_char(systimestamp) from dual;
TO_CHAR(SYSTIMESTAMP)
--------------------------------------------------------------
2011-07-13 21:43:18 984000 +08:00
4
、结论
timestamp
系列类型是对
Oracle Date
类型的一种有益补充。在需要的场合下，可以适当使用。
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
