开发者社区> 小桥河西> 正文

关于数据库中的TIMESTAMP WITH TIME ZONE

简介: 关于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
  1. TIMESTAMP and TIME may also be specified as being WITH TIME ZONE, in which case every value has
  2. associated with it a time zone displacement. In comparing values of a data type WITH TIME ZONE, the value
  3. 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

  1. TIMESTAMP Data Type
  2. 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.
  3. ...
  4. TIMESTAMP WITH TIME ZONE Data Type
  5. 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.
  6. ...
  7. TIMESTAMP WITH LOCAL TIME ZONE Data Type
  8. 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
  1. That is not the case. The existing implementation is work that Tom
  2. Lockhart did around 6.3 or so. It was called timestamp at the time,
  3. and was renamed to timestamp with time zone in 7.2, in order to make
  4. room for timestamp without time zone (which I think *is* spec compliant
  5. or close enough). That was probably an unfortunate choice; but at
  6. no time was there code in PG that did what the spec says timestamp
  7. with time zone should do.


  8. 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/


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
The server time zone value ‘‘ is unrecognized or represents more than one time zone.
The server time zone value ‘‘ is unrecognized or represents more than one time zone.
10 0
Date & Time组件(上)
本节给大家带来的是Android给我们提供的显示时间的几个控件,他们分别是: TextClock,AnalogClock,Chronometer,另外其实还有个过时的DigitalClock就不讲解了! 好的,开始本节内容!
38 0
The server time zone value '?й???????' is unrecognized or represents more than one time zone.
The server time zone value '?й???????' is unrecognized or represents more than one time zone.
30 0
MySQL:自动维护create_time和update_time字段
通过建表语句设置,让mysql自动维护这两个字段,那么编程的时候也能少写一部分代码
28 0
MySQL中date、datetime、timestamp、time、year的区别
MySQL中date、datetime、timestamp、time、year的区别
56 0
mysql:TIMESTAMP在UPDATE CURRENT_TIMESTAMP数据类型上做什么?
mysql:TIMESTAMP在UPDATE CURRENT_TIMESTAMP数据类型上做什么?
42 0
JDBC - The server time zone value ‘???‘ is unrecognized or represents more than one time zone
JDBC - The server time zone value ‘???‘ is unrecognized or represents more than one time zone
40 0
【Mysql】字段类型datetime,timeStamp,time的区别
【Mysql】字段类型datetime,timeStamp,time的区别
66 0
Mysql - date、datetime、timestamp 的区别
Mysql - date、datetime、timestamp 的区别
479 0
timestamp的两个属性:CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP
timestamp的两个属性:CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP
218 0
+关注
小桥河西
半个PostgreSQL DBA,热衷于数据库及分布式技术。 - https://github.com/ChenHuajun - https://pan.baidu.com/s/1eRQsdAa
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
Ream-time analytical query
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载