不管是笑与否——我没有什么货，这篇文章是在真正的没有章法后的简单的剪切粘贴。
20:39:51 SQL> create table t1 (t1 timestamp);
Table created.
20:39:55 SQL> insert into t1 values(systimestamp);
1 row created.
20:39:59 SQL> select t1 - systimestamp from t1;
T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620
1 row selected.
20:40:08 SQL>
我的笔记本电脑运行Oracle很流畅，仅仅在5小时之内花了4秒消逝。运行在64位的Linux系统的Oracle 11.2.0.3 ——客户端以TZ=EST5EDT运行，但服务器端以UK时运行（目前BST（GMT+1））。
在MOS: 340512.1的时间戳和时区评论可用——关于MOS的常问问题，幸亏Jure Bratina，在这个问题上评论，227334.1——“日期和日历——常问问题”。
更新：
正像Niall在这个评论里引用的，“时间是很复杂的”。
Oracle提供三个时间戳：systimestamp，localtimestamp和current_timestamp（根据很多一致性原则，仅仅有一个使用下划线）。Oracle也提供三个时间戳类型：timestamp，timestamp with time zone和timestamp with local time zone。Oracle同时也提供两种时区，叫做dbtimezone和sessiontimezone。
如果你需要弄清楚这些联系起来的细节，我认为你需要设置你机器的时区为非UTC（或者是GMT我仍然倾向于这样叫它），然后使用两个分离的机器做为客户端，它们的时区设置成其他时区（）再次避免UTC。
我做了很多实验，但是我的设置还不够严谨——我的机器时区是GMT，但是我打开一个（UNIX机器）会话设置会话时区为EST5EDT然后启动我的数据库，接着运行另一个（UNIX机器）会话，设置不同的时区。在不同的时区我应该重启机器的原因是Oracle正常化的timestamps设置成UTC——意思是说有几种情况，当我不能确定存储的值是是否在UTC里，因为它被正常化或者因为仅仅是真实的机器时间。
隐藏这里有个小小的实验（11.2.0.2，实例以EST5EDT允许，unix会话以UTC允许，通过网络跟服务器取得连接）。
select
current_timestamp,
localtimestamp,
systimestamp
from
dual
;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
17-APR-13 11.37.10.870658 AM +01:00
17-APR-13 11.37.10.870658 AM
17-APR-13 06.37.10.870554 AM -04:00
注意：
systimestamp反映实例的timestamp——比会话timestamp早五个小时。systimestamp返回带时区的timestamp，不仅仅是一个timestamp。localtimestamp和current_timestamp显示客户端时间，但是localtimestamp不显示时区，current_timestamp要显示（+1:00出现了因为夏令时（英国夏令时间）被激活了因此我的会话比UTC提前了一个小时，然后数据库滞后了4小时）。
另一个快速测试：
create table t1 (
t0 timestamp,
tz timestamp with time zone,
tl timestamp with local time zone,
ts_type varchar2(20)
)
;
insert into t1 values(
systimestamp, systimestamp, systimestamp,
'sys Timestamp'
);
commit;
select * from t1;
T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL TS_TYPE
--------------------------------------------------------------------------- --------------------
17-APR-13 06.44.04.353489 AM
17-APR-13 06.44.04.353489 AM -04:00
17-APR-13 11.44.04.353489 AM sys Timestamp
select
dump(t0,16),
dump(tz,16),
dump(tl,16),
ts_type
from
t1
;
DUMP(T0,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TZ,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TL,16)
------------------------------------------------------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,71,4,11,7,2d,5,15,11,d0,68
Typ=181 Len=13: 78,71,4,11,b,2d,5,15,11,d0,68,10,3c
Typ=231 Len=11: 78,71,4,11,b,2d,5,15,11,d0,68
sys Timestamp
注意：
T0：timestamp列，有实例的timestamp——但是没有存储任何时区信息；原始转储显示的值是6:44:04（7,2d,5——转换为十六进制然后减去1）。任何人在任何时区都会看到输出显示6:44:04如果他们选择这一列。
TZ——the timestamp with time zone列，有实例的timestamp，但是存储（b, 2d,5 – 11:44:04）有时区信息（10,3c），允许会话知晓全局的时间和地区（或者更一进步，时区）信息。
TL——the timestamp with local time zone列，有实例的timestamp，但没有存储（b, 2d, 5 – 11:44:04）时区信息。因此当你查询时，输出的结果被调整为适合长的时间戳。这是正确的全局时刻，显示相关的本地时间。但是，作为惩罚，将会丢失关于进入（在哪个时区）的信息。
我认为这个关于原始转储的三个不同类型的内容的测验可以帮助你明白你为什么需要在一个列里存储包含时区——如果你不想丢失一些信息，基于时间的算术将会给你一些惊喜，如果你的应用是跨时区的话，
下一个话题：
索引时间（虽然在Tony Hasler’s blog的评论里的连接或许可以所有你想要的答案），Oracle设计的错误我曾经访问过。
我曾经访问过：
http://jonathanlewis.wordpress.com/2010/04/05/failed-login/
For your entertainment – there’s nothing up my sleeves, this was a simple cut-n-paste after real-time typing with no tricks:
20:39:51 SQL> create table t1 (t1 timestamp);
Table created.
20:39:55 SQL> insert into t1 values(systimestamp);
1 row created.
20:39:59 SQL> select t1 - systimestamp from t1;
T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620
1 row selected.
20:40:08 SQL>
My laptop runs Oracle so quickly that it took only 4 seconds for 5 hours to elapse !
11.2.0.3 on 64-bit Linux – the client is running with TZ=EST5EDT, while the server is running UK Time (currently BST (GMT+1))
Comments available on MOS: 340512.1 Timestamps & time zones – Frequently Asked Questions
Another MOS note, thanks to Jure Bratina in the comments: 227334.1 – “Dates & Calendars – Frequently Asked Questions” in the question
Update:
As Niall quotes in the comments: “times are difficult”.
Oracle supplies three timestamps: systimestamp, localtimestamp, and current_timestamp. (For reasons of consistency, only one of uses an underscore ;) )
Oracle also supplies three timestamp types: timestamp, timestamp with time zone, and timestamp with local time zone.
Oracle also supplies two timezone calls: dbtimezone, and sessiontimezone
If you need to figure out all the details of how these things hang together, I think you need to set your machine timezone to something that isn’t UTC (or GMT as I still tend to call it), then use two separate machines as clients, with their timezones set to two other timezones (again avoiding UTC).
I’ve done a few experiments but without being so rigorous in my settings – my machine was running on GMT, but I opened a (UNIX) session and set the session time zone to EST5EDT to start the database, while running other (UNIX) session with different TZ settings. The reason I should have restarted the machine in a different timezone is that Oracle “normalises” some timestamps to UTC – which means there are cases when I can’t be certain whether the stored value is in UTC because it has been normalised or because it simply was the actual machine time.
So here’s a little experiment (11.2.0.2, instance started in EST5EDT, unix session running in UTC, connecting across the network to the server).
select
current_timestamp,
localtimestamp,
systimestamp
from
dual
;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
17-APR-13 11.37.10.870658 AM +01:00
17-APR-13 11.37.10.870658 AM
17-APR-13 06.37.10.870554 AM -04:00
Notes:
systimestamp reflects the instance timestamp – which is 5 hours earlier than the session timestamp.
systimestamp returns a timestamp with time zone, not just a timestamp
localtimestamp and current_timestamp show the client time, but localtimestamp doesn’t show the timezone, current_timestamp does (the +1:00 appears because Daylight Saving Time (British Summer Time) is active so my session is one hour ahead of UTC, while the database is 4 hours behind.)
Another quick test:
create table t1 (
t0 timestamp,
tz timestamp with time zone,
tl timestamp with local time zone,
ts_type varchar2(20)
)
;
insert into t1 values(
systimestamp, systimestamp, systimestamp,
'sys Timestamp'
);
commit;
select * from t1;
T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL TS_TYPE
--------------------------------------------------------------------------- --------------------
17-APR-13 06.44.04.353489 AM
17-APR-13 06.44.04.353489 AM -04:00
17-APR-13 11.44.04.353489 AM sys Timestamp
select
dump(t0,16),
dump(tz,16),
dump(tl,16),
ts_type
from
t1
;
DUMP(T0,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TZ,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TL,16)
------------------------------------------------------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,71,4,11,7,2d,5,15,11,d0,68
Typ=181 Len=13: 78,71,4,11,b,2d,5,15,11,d0,68,10,3c
Typ=231 Len=11: 78,71,4,11,b,2d,5,15,11,d0,68
sys Timestamp
Notes:
T0 – the timestamp column, has the instance timestamp in it – but doesn’t have any timezone information stored; the raw dump show the value 6:44:04 (7, 2d, 5 – convert from hex and substract one). Anyone on ANY timezone will see their output showing 6:44:04 if they select this column.
TZ – the timestamp with time zone column, has the instance timestamp, but has stored it as (b, 2d,5 – 11:44:04) with time zone information (10,3c) that allows the session to know what “global” moment the information really represents and the location (or, rather, time zone) where is was entered.
TL – the timestamp with local time zone, has the instance timestamp, but has stored it as (b, 2d, 5 – 11:44:04) with NO timezone information. So the output when you query this column is adjusted to suit the local timestamp. It’s the right “global” moment, and it displays as the relevant local time. But, as a penalty, it’s lost the information about where (in which time zone) it was entered.
I think that examination of the content of the raw dumps of the three different types may help you understand why you need to store timestamps in a column type that includes a time zone – if you don’t then you lose some information, and time-based arithmetic will give you some surprises if your application crosses timezones.
Next Issue:
Indexing time (though the link in the comments below to Tony Hasler’s blog probably gives you all the answers you need), and an Oracle design error that I’ve visited before.
I’ve visited before.
http://jonathanlewis.wordpress.com/2010/04/05/failed-login/
http://www.oaktable.net/content/systimestamp
 |   |
| @Wentasy 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客] |
扫一扫
588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
