关于数据库中的TIMESTAMP WITH TIME ZONE-阿里云开发者社区

关于数据库中的TIMESTAMP WITH TIME ZONE

2014-12-25 1666

简介： 关于TIMESTAMP WITH TIME ZONE，SQL标准中有这么一段描述 SQL2008 TIMESTAMP and TIME may also be specified as being WITH TIME ZONE, in which case ever...

+关注继续查看

关于TIMESTAMP WITH TIME ZONE，SQL标准中有这么一段描述

SQL2008

TIMESTAMP and TIME may also be specified as being WITH TIME ZONE, in which case every value has
associated with it a time zone displacement. In comparing values of a data type WITH TIME ZONE, the value
of the time zone displacement is disregarded.

SQL标准好像没有明确说附加的TIME ZONE要不要作为一个值存下来，具体到不同数据库，对TIMESTAMP WITH TIME ZONE的实现还是有差异的。

Oracle

Oracle的功能最完善，Oracle有3种TIMESTAMP类型,分别是
TIMESTAMP
无时区概念，存什么时间（年月日时分秒）进去取出来就是什么时间。
TIMESTAMP WITH LOCAL TIME ZONE
按database timezone存储时间，取数据时按用户session的时区时间返回给用户，取出来的时间是不带时区的。
Oracle建议把database timezone设置为标准时间UTC，这样可以节省每次转换所需要的开销，提高性能。
TIMESTAMP WITH TIME ZONE
时间和时区都被存下来，取数据时能获得当初存储的时间和时区。

以下是Oracel手册中的描述
http://docs.oracle.com/database/121/SQLRF/toc.htm

TIMESTAMP Data Type
The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type, plus hour, minute, and second values. This data type is useful for storing precise time values and for collecting and evaluating date information across geographic regions.
...
TIMESTAMP WITH TIME ZONE Data Type
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for preserving local time zone information.
...
TIMESTAMP WITH LOCAL TIME ZONE Data Type
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that is sensitive to time zone information. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user

SQL Server

SQL Server有个奇葩的地方，就是它的“timestamp”其实不是时间日期类型，而是行版本。SQL Server中和SQL标准的TIMESTAMP对应是下面的数据类型：
datetime/datetime2
对应于SQL标准TIMESTAMP
datetimeoffset
SQL Server2008新加的数据类型，是时间+时区偏移量的组合，相当于SQL标准TIMESTAMP WITH TIME ZONE。

PostgreSQL

PostgreSQL也有奇葩的地方，它的“TIMESTAMP WITH TIME ZONE”从名字上很容易给人不该有的期望。
TIMESTAMP
对应于SQL标准TIMESTAMP
TIMESTAMP WITH TIME ZONE
内部是按UTC时区存储的时间，客户端读写该数据时PostgreSQL服务器根据用户session的timezone和UTC的差距进行时间转换。由于并没有把原始的时区值存下来，所以PostgreSQL的“TIMESTAMP WITH TIME ZONE”其实相当于Oracle的“TIMESTAMP WITH LOCAL TIME ZONE”。但是PostgreSQL中名称类似的“TIME WITH TIME ZONE”数据里却是存储了时区值的。所以如果确实需要得到原始的时区可以定义一个(date,timetz)的组合类型。

关于PostgreSQL的“TIMESTAMP WITH TIME ZONE”，今年PG社区有个讨论，原来“TIMESTAMP WITH TIME ZONE”是从以前的一个数据类型改名改过来的，并且现在看来改的不太理想。

http://www.postgresql.org/message-id/19136.1409325534@sss.pgh.pa.us

That is not the case. The existing implementation is work that Tom
Lockhart did around 6.3 or so. It was called timestamp at the time,
and was renamed to timestamp with time zone in 7.2, in order to make
room for timestamp without time zone (which I think *is* spec compliant
or close enough). That was probably an unfortunate choice; but at
no time was there code in PG that did what the spec says timestamp
with time zone should do.
regards, tom lane

参考：

http://www.postgresql.org/docs/9.4/static/datatype-datetime.html
http://msdn.microsoft.com/zh-cn/library/bb630289.aspx
http://blog.itpub.net/28502651/viewspace-766329/