Oracle时间戳类型

 

Oracle时间戳类型

    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)

 

 

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

1. 使用TIMESTAMP类型

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

Sql代码 

 

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关键字，格式如下：

Sql代码 

 

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

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

 

Sql代码 

 

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

     将被取整为：

Sql代码 

 

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

 

 

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

Sql代码 

 

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表进行检索：

Sql代码 

 

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，该列用来记录该顾客在何时购买了产品：

Sql代码 

 

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：

 

Sql代码 

 

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表进行检索：

Sql代码 

 

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，该列用来记录顾客在何时购买了产品：

 

Sql代码 

 

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：

 

Sql代码 

 

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表进行检索：

Sql代码 

 

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：

 

Sql代码 

 

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：

 

Sql代码 

 

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

 

 

转载自:http://tivan.iteye.com/blog/772431