Chinaunix首页 | 论坛 | 博客
  • 博客访问: 7736
  • 博文数量: 1
  • 博客积分: 285
  • 博客等级: 民兵
  • 技术积分: 15
  • 用 户 组: 普通用户
  • 注册时间: 2011-11-07 12:28
文章分类
文章存档

2012年(1)

我的朋友

分类:

2012-05-10 13:40:05

原文地址:Oracle中的TIMESTAMP类型解读 作者:ok2ok

SQL> create table test_time (col_time timestamp);

表已创建。

SQL> insert into test_time values (to_timestamp('0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> insert into test_time values (to_timestamp('9999-12-31 23:59:59.999999', 'syyyy-mm-dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> insert into test_time values (to_timestamp('-0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> insert into test_time values (to_timestamp('-0100-3-4 13:2:3.234015', 'syyyy-mm-dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> insert into test_time values (systimestamp);

已创建 1 行。


SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));

已创建 1 行。

SQL> commit;

提交完成。

SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
  2  from test_time;

TIME                           DUMP_TIME
------------------------------ ----------------------------------------------------
 0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
 2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
 9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
 2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
 2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232

已选择7行。

DATE类型对比可以发现,对于TIMESTAMP类型,如果不包含微秒信息或者微秒值为0,那么存储结果和DATE完全相同。当微秒值为0时,Oracle为了节省空间,不会保存微秒信息。

如果毫秒值不为0,Oracle把微秒值当作一个9位数的数字来保存。

比如999999000,保存为59,154,198,24。234015000保存为13,242,201,24。

SQL> select to_char(999999000, 'xxxxxxxxxx') from dual;

TO_CHAR(999
-----------
   3b9ac618

SQL> select to_number('3b', 'xxx') one, to_number('9a', 'xxx') two,
  2  to_number('c6', 'xxx') three, to_number('18', 'xxx') four from dual;

       ONE        TWO      THREE       FOUR
---------- ---------- ---------- ----------
        59        154        198         24

SQL> select to_char(234015000, 'xxxxxxxx') from dual;

TO_CHAR(2
---------
  df2c918

SQL> select to_number('d', 'xxx') one, to_number('f2', 'xxx') two,
  2  to_number('c9', 'xxx') three, to_number('18', 'xxx') four from dual;

       ONE        TWO      THREE       FOUR
---------- ---------- ---------- ----------
        13        242        201         24

 

另外,注意一点,不指定精度的情况下,TIMESTAMP默认取6位。长度超过6位,会四舍五入到6位。如果希望保存9位的TIMESTAMP,必须明确指定精度。

SQL> alter table test_time modify (col_time timestamp(9));

表已更改。

SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));

已创建 1 行。

SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
  2  from test_time;

TIME                           DUMP_TIME
------------------------------ ---------------------------------------------------
 0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
 2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
 9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
 2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
 2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232
 2000-01-01 00:00:00.123456789 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,21

已选择8行
 
==============================================

select systimestamp from dual

select current_timestamp from dual

select cast(sysdate as timestamp) "DATE" from dual

select to_timestamp(to_date(sysdate, 'yyyy-mm-dd')) from dual

select to_timestamp('2008-11-17 00:31:35', 'YYYY-MM-DD HH24:MI:SS:FF') from dual

在ORACLE里面TIMESTAMP保存的格式是什么?insert into table(timestamp_field)values(sysdate)

insert into table(timestamp_field)values(to_timestamp(to_date('2007-11-1','yyyy-mm-dd'))
保存后

timestamp_field
-------------------------------
01-11月-07 12.00.00.000000 上午

如果你想在ORACLE中存储DATE和时间信息的话,实际上你由两种字段数据类型的选择的话,就让我们看看这两种数据类型的差别和它们提供了些什么。



      DATE数据类型

这个数据类型我们实在是太熟悉了,当我们需要表示日期和时间的话都会想到date类型。它可以存储月,年,日,世纪,时,分和秒。它典型地用来表示什么时候事情已经发生或将要发生。DATE数据类型的问题在于它表示两个事件发生时间间隔的度量粒度是秒。这个问题将在文章稍后讨论timestamp的时候被解决。可以使用TO_CHAR函数把DATE数据进行传统地包装,达到表示成多种格式的目的。

SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;

Date

---------------------------

06/20/2003 16:55:14

06/26/2003 11:16:36



我见到的大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是,当你进行两个日期的相减运算的时候,得到的是天数。你需要乘上每天的秒数(1天=86400秒),然后,你可以再次计算得到你想要的间隔数。下面就是我的解决方法,可以精确计算出两个时间的间隔。我明白这个例子可以更简短些,但是我是为了显示所有的数字来强调计算方式。

1         SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,

2         TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,

3         trunc(86400*(date2-date1))-

4         60*(trunc((86400*(date2-date1))/60)) seconds,

5         trunc((86400*(date2-date1))/60)-

6         60*(trunc(((86400*(date2-date1))/60)/60)) minutes,

7         trunc(((86400*(date2-date1))/60)/60)-

8         24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,

9         trunc((((86400*(date2-date1))/60)/60)/24) days,

10         trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks

11*        FROM date_table

DATE1             DATE2                SECONDS    MINUTES     HOURS      DAYS      WEEKS

----------------- ----------------- ---------- ---------- ---------- ---------- ----------

06202003:16:55:14 07082003:11:22:57         43         27         18        17          2

06262003:11:16:36 07082003:11:22:57         21           6          0        12          1



TIMESTAMP 数据类型

DATE数据类型的主要问题是它粒度不能足够区别出两个事件哪个先发生。ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型,它包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型,就使用CAST函数。

SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;

Date

-----------------------------------------------------

20-JUN-03 04.55.14.000000 PM

26-JUN-03 11.16.36.000000 AM



正如你看到的,在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候,没有小数秒的信息,缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候,可以直接写INSERT SELECT语句,oracle会自动为你做转换的。

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table

Date

-------------------

06/20/2003 16:55:14

06/26/2003 11:16:36

TIMESTAMP数据的格式化显示和DATE 数据一样。注意,to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下,使用TIMESTAMP数据类型要比DATE数据类型更确切。
  
  如果你想显示TIMESTAMP的小数秒信息,参考下面:
  
  1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
  
  Date
  
  -----------------------
  
  06/20/2003 16:55:14:000
  
  06/26/2003 11:16:36:000
  
  在上例中,我只现实了小数点后3位的内容。
  
  计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话,看看会发生什么。结果将更容易理解,第一行的17天,18小时,27分钟和43秒。
  
  1 SELECT time1,
  
   2     time2,
  
   3     substr((time2-time1),instr((time2-time1),' ')+7,2)         seconds,
  
   4     substr((time2-time1),instr((time2-time1),' ')+4,2)         minutes,
  
   5     substr((time2-time1),instr((time2-time1),' ')+1,2)         hours,
  
   6     trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))  days,
  
   7     trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
  
   8*  FROM date_table
  
  TIME1            TIME2           SECONDS MINUTES HOURS DAYS WEEKS
  
  -------------------------  -------------------------- ------- ------- ----- ---- -----
  
  06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43   27   18  17  2
  
  06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21   06   00  12  1
  
  这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此,得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了。
  
  系统日期和时间
  
  为了得到系统时间,返回成date数据类型。你可以使用sysdate函数。
  
  SQL> SELECT SYSDATE FROM DUAL;
  
  为了得到系统时间,返回成timestamp数据类型。你可以使用systimpstamp函数。
  
  SQL> SELECT SYSTIMESTAMP FROM DUAL;
  
  你可以设置初始化参数FIXED_DATE指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意,这个参数对于systimestamp函数无效。
  
  SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
  
  System altered.
  
  SQL> select sysdate from dual;
  
  SYSDATE
  
  ---------
  
  01-JAN-03
  
  SQL> select systimestamp from dual;
  
  SYSTIMESTAMP
  
  ---------------------------------------------------------
  
  09-JUL-03 11.05.02.519000 AM -06:00
  
  当使用date和timestamp类型的时候,选择是很清楚的。你可以随意处置date和timestamp类型。当你试图转换到更强大的timestamp的时候,需要注意,它们既有类似的地方,更有不同的地方,而足以造成破坏。两者在简洁和间隔尺寸方面各有优势,请合理地选择。

阅读(2599) | 评论(0) | 转发(0) |
0

上一篇:没有了

下一篇:没有了

给主人留下些什么吧!~~