oracle中的timestamp格式简介

最近部门外包了子公司的一套系统，一开始没有考虑到跨时区使用的问题，因此没有对系统做国际化，等到接近正式上线了才发现时区的问题，原本的打算是在系统中设置一个字段，让用户自己选择对应的时区，然后系统中时间格式的字段都按照该时区进行转换。然而这样一来，几乎整套系统全都要进行修改，工作量太大了。在某一次跟项目经理聊天的过程中，运维大哥凑过来说其实 Oracle 有个数据类型在读取的时候可以将时间转换成客户端当前时区的时间。

我怀着半信半疑的心百度了一下，结果发现 Oracle 数据库确实有这样的数据类型，分别叫做 Timestamp、Timestamp with local time zone ，以及 Timestamp with time zone ，其中timestamp with local time zone 完全就是我们当前系统所需要的数据类型。经过研究，目前已经了解了该数据类型应该如何使用。

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，比如查询数据库的时区 dbtimezone 以及查询会话的时区sessiontimezone ，执行以下 sql ：

select dbtimezone, sessiontimezone from dual; 

查询的结果如下：

可以看到，该数据库的时区是标准时区（UTC+00:00），而当前会话时区是中国标准时间（UTC+08:00）。

接着创建时区测试表，

create table timetest(tme date,tmestp timestamp(3), tmestp_tz timestamp(3) with time zone, tmpstp_tzl timestamp(3) with local time zone);

将当前时间插入到测试表中：

insert into timetest(TME,TMESTP,TMESTP_TZ,TMPSTP_TZL) values(sysdate,sysdate,sysdate,sysdate);   

查询测试表数据：

select * from timetest; 

查询结果如下图：

从查询结果我们可以看出来，

TME 字段类型为 date ，因此保存的日期格式为 yyyy-mm-dd hh24:mi:ss；

TMESTP 字段类型为 timestamp ，因此保存的时间会带上毫秒；

TMESTP_TZ 字段类型为 timestamp with time zone ，从字段类型名称我们不难猜出这个字段是在 timestamp 的基础上加上时区信息，因此 TMESTP_TZ 该字段保存了当前的时区，也就是 +08:00；

TMPSTP_TZL 字段类型 timestamp with local time zone ，该字段类型不保存时区信息，在保存到数据库时，会将时间转换成数据库时区（dbtimezone）对应的时间保存到数据库中，读取的时候会按照会话时区（sessiontimezone ）转换成对应的时间。

接下来我们修改一下本地电脑的时区，使其成为 UTC+10:00

注意：修改完时区后要重新连接数据库，否则 sessiontimezone 还是之前的 +08:00

重新连接数据库之后，再次查询系统时区以及会话时区：

select dbtimezone, sessiontimezone from dual; 

查询结果如下图，sessiontimezone 已经变成了 +10:00

这时候重新查询测试表的数据：

select * from timetest; 

查询结果如下图：

我们会发现，前面三个字段都没有任何变化，但是 TMPSTP_TZL 字段的时间变成 18时，是在之前的基础上加上2个小时，之前是 UTC+08:00 时区，现在是 UTC+10:00 时区，因此多两个小时是正确的。

因此，在跨时区的系统里面，将时间相关的字段按照需求设置为 timestamp with local time zone 是很有必要的，这样子同一个时间，在北京看到的就是 08:00，在伦敦看到的就是 00:00。如果需要保存到具体的时区信息的话，则可以使用 timestamp with time zone 类型，则不管是在北京还是在伦敦，看到的都是 08:00 +08:00，这样子就能清楚地知道这是 UTC+08:00 时区的早上8点。