Oracle时间戳

上一篇 / 下一篇  2010-12-16 09:52:20

查看( 1496 ) / 评论( 0 ) / 评分( 0 / 0 )
Oracle Databse 9i数据库引入了一种新特性，可以存储时间戳。时间戳可以存储世纪、4位年、月、日、时(以24小时格式)、分、秒。与DATE类型相比，时间戳具有以下优点：

    ● 时间戳可以存储秒的小数位。

    ● 时间戳可以存储时区。

    下面介绍时间戳类型。

 使用时间戳类型

    时间戳有3种类型，如表所示。

    表  时间戳类型

类    型说    明
TIMESTAMP[(seconds_precision)]存 储世纪、4位的年、月、日、时(以24小时格式)、分和秒。seconds_precision为可选参数，用于指定精度，该参数为一个整数，范围从 0~9，默认值为9；意思是表示秒的数字的小数点右边可以存储9位数字。如果试图在秒的小数中保存超过TIMESTAMP存储精度的数字，那么该小数将被 取整
TIMESTAMP[(seconds_precision)]WITH TIME ZONE扩展TIMESTAMP，用于存储时区
TIMESTAMP[(seconds_precision)]WITH LOCAL TIME ZONE扩展TIMESTAMP，将给定的时间值转换成数据库的本地时区。转换过程称为时间值的规格化(normalizing)

 

我的例子：

create table test_timestamp (
id integer primary key,name varchar2(20),time timestamp(9)
);
insert into test_timestamp values(1,'tester1',systimestamp);
select * from test_timestamp;

   接下来的几节将介绍如何使用这些时间戳。

1. 使用TIMESTAMP类型

    与其他类型类似，TIMESTAMP类型也可以用来在表中定义列。下面这个语句用于创建一个表purchases_with_timestamp，该表用来存储顾客的购买记录。该表包含了一个TIMESTAMP类型的列made_on，该列用来记录顾客在何时购买了产品。注意TIMESTAMP的精度设置为4(这意味着在秒的小数点右边可以存储4位小数)：


  1.  CREATE TABLE purchases_with_timestamp (   
  2. product_id INTEGER REFERENCES products(product_id),   
  3. customer_id INTEGER REFERENCES customers(customer_id),   
  4. made_on TIMESTAMP(4)   
  5. );  
 

 

注意：

purchases_with_timestamp表由store_schema.sql脚本创建和填充。本章的其他表也是由该脚本创建，因此不需要输入CREATE TABLE语句或本章所示的任何INSERT语句。

要向数据库提供一个TIMESTAMP字面值，可以在一个时间值前面指定TIMESTAMP关键字，格式如下：


  1. TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.SSSSSSSSS'  

     注意在小数点后面有9个S字符，这意味着可以在字符串中为秒的小数部分指定9个数字。TIMESTAMP列中可以实际存储多少位数字取决于在定义该列时为秒的小数部分指定了多少位数字。例如，purchases_with_timestamp表的made_on列可以存储最多4位小数的秒数。如果试图向该表中添加一行秒的精度超过4位小数的记录，小数部分就会被取整。例如：

 


  1. 2005-05-13 07:15:31.123456789  

     将被取整为：


  1. 2005-05-13 07:15:31.1235  
 

 

    下面这个INSERT语句向purchases_with_timestamp表中添加一行记录，注意此处使用了TIMESTAMP关键字指定一个时间值：


  1. INSERT INTO purchases_with_timestamp (   
  2. product_id, customer_id, made_on   
  3. ) VALUES (   
  4. 1, 1, TIMESTAMP '2005-05-13 07:15:31.1234'  
  5. );  
 

    下面这个查询对purchases_with_timestamp表进行检索：


  1.  SELECT *   
  2. FROM purchases_with_timestamp;   
  3.   
  4. PRODUCT_ID CUSTOMER_ID  MADE_ON   
  5. ---------- -----------  --------------------------  
  6. 1              1 13-MAY-05 07.15.31.1234 AM  

 

2. 使用TIMESTAMP WITH TIME ZONE类型

TIMESTAMP WITH TIME ZONE类型对TIMESTAMP进行了扩展，用于存储时区。下面这个语句创建一个表purchases_timestamp_with_tz，该表用于存储顾客的购买记录。该表包含了一个TIMESTAMP WITH TIME ZONE类型的列made_on，该列用来记录该顾客在何时购买了产品：


  1.  CREATE TABLE purchases_timestamp_with_tz (   
  2. product_id INTEGER REFERENCES products(product_id),   
  3. customer_id INTEGER REFERENCES customers(customer_id),   
  4. made_on TIMESTAMP(4) WITH TIME ZONE   
  5. );  
 

要向数据库提供一个带有时区信息的时间戳，可以在TIMESTAMP子句中加上时区信息。例如，下面这个TIMESTAMP子句包含了时差-07:00：

 


  1.  TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'  
  2. 此处也可以指定一个时区名，下面这个例子就指定PST作为时区使用：   
  3. TIMESTAMP '2005-05-13 07:15:31.1234 PST'  
  4. 下面这个INSERT语句向purchases_timestamp_with_tz表中添加两行记录：   
  5. INSERT INTO purchases_timestamp_with_tz (   
  6. product_id, customer_id, made_on   
  7. ) VALUES (   
  8. 1, 1, TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'  
  9. );   
  10.   
  11. INSERT INTO purchases_timestamp_with_tz (   
  12. product_id, customer_id, made_on   
  13. ) VALUES (   
  14. 1, 2, TIMESTAMP '2005-05-13 07:15:31.1234 PST'  
  15. );  
 

下面这个查询对purchases_timestamp_with_tz表进行检索：


  1.  SELECT *   
  2. FROM purchases_timestamp_with_tz;   
  3.   
  4. PRODUCT_ID  CUSTOMER_ID MADE_ON   
  5. ----------  ----------- ---------------------------------  
  6. 1             1  13-MAY-05 07.15.31.1234 AM -07:00   
  7. 1             2  13-MAY-05 07.15.31.1234 AM PST  

 

3. 使用TIMESTAMP WITH LOCAL TIME ZONE类型

TIMESTAMP WITH LOCAL TIME ZONE类型对TIMESTAMP进行了扩展，用于存储一个转换为数据库设置的本地时区的时间戳。在为一个TIMESTAMP WITH LOCAL TIME ZONE类型的列存储一个时间戳值时，这个时间戳就被转换(或被规格化)为数据库的时区设置。当检索这个时间戳时，它就被规格化为当前会话中的时区设置。

提示：

如果企业已经实现了一个全球系统，可以通过世界各地访问，那么就应该使用TIMESTAMP WITH LOCAL TIME ZONE类型来存储时间戳。这是因为数据库会将时间戳存储为数据库所在时区中的本地时间，但是在对它们进行检索时，却可以看到规格化为本地时区的时间戳。

假设数据库时区是PST(比UTC晚8个小时)，想向数据库中存储下面的时间戳：

 

2005-05-13 07:15:30 EST

 

由于EST比UTC晚5个小时，因此EST与PST之间差3个小时(8 - 5 = 3)。要将前面的时间戳规格化为PST，就必须从时间戳中减去3个小时就得到下面的规格化的时间戳：

 

2005-05-13 04:15:30

 

这是数据库的TIMESTAMP WITH LOCAL TIME ZONE列中所存储的时间戳。

下面这个语句创建一个表purchases_ with_local_tz，该表用于存储顾客的购买记录。该表包含了一个TIMESTAMP WITH LOCAL TIME ZONE类型的列made_on，该列用来记录顾客在何时购买了产品：

 


  1.  CREATE TABLE purchases_with_local_tz (   
  2. product_id INTEGER REFERENCES products(product_id),   
  3. customer_id INTEGER REFERENCES customers(customer_id),   
  4. made_on TIMESTAMP(4) WITH LOCAL TIME ZONE   
  5. );  

 下面这个INSERT语句向purchases_ with_local_tz表中添加一行记录，并将该行的made_on列值设置为2005-05-13 07:15:30 EST：

 


  1.  INSERT INTO purchases_with_local_tz (   
  2. product_id, customer_id, made_on   
  3. ) VALUES (   
  4. 1, 1, TIMESTAMP '2005-05-13 07:15:30 EST'  
  5. );  

 

虽然made_on列的时间戳设置为2005-05-13 07:15:30 EST，但数据库中实际存储的时间戳是2005-05-13 04:15:30(时间戳规格化为PST)。

下面这个查询对purchases_ with_local_tz表进行检索：


  1.  SELECT *   
  2. FROM purchases_with_local_tz;   
  3.   
  4. PRODUCT_ID  CUSTOMER_ID  MADE_ON   
  5. ----------  -----------  --------------------------  
  6. 1             1   13-MAY-05 04.15.30.0000 AM  
 

因为我的数据库时区和会话时区都是PST，所以此查询返回的时间戳都是针对PST的。

警告：

前面这个查询返回的时间戳规格化为PST。如果数据库时区或会话时区不是PST，则运行查询时返回的时间戳会有所不同(它将规格化为您的时区)。

如果将当前会话的本地时区设置为EST，并重新执行上面这个查询，就会看到时间戳被规格化为EST：

 


  1.  ALTER SESSION SET TIME_ZONE = 'EST';   
  2.   
  3. Session altered.   
  4.   
  5. SELECT *   
  6. FROM purchases_with_local_tz;   
  7.   
  8. PRODUCT_ID CUSTOMER_ID  MADE_ON   
  9. ---------- -----------  --------------------------  
  10. 1             1   13-MAY-05 07.15.30.0000 AM  

 

可以看到，此查询返回的时间戳是13-MAY-05 07.15.30.0000 AM，这是规格化为会话时区EST的时间戳。因为EST比PST早3小时，所以必须将13-MAY-05 04:15:30(数据库中存储的时间戳)增加3小时，得到13-MAY-05 07.15.30 AM(查询返回的时间戳)。

下面这条语句将会话时区设置回PST：

 


  1. ALTER SESSION SET TIME_ZONE = 'PST';   
  2.   
  3. ession altered.  

收藏 举报

TAG:

 

评分：0

我来说两句

Open Toolbar