`

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位小数):

 CREATE TABLE purchases_with_timestamp (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4)
);
 

 

注意:

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

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

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

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

 

2005-05-13 07:15:31.123456789

     将被取整为:

2005-05-13 07:15:31.1235
 

 

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

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

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

 SELECT *
FROM purchases_with_timestamp;

PRODUCT_ID CUSTOMER_ID  MADE_ON
---------- -----------  --------------------------
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,该列用来记录该顾客在何时购买了产品:

 CREATE TABLE purchases_timestamp_with_tz (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4) WITH TIME ZONE
);
 

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

 

 TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
此处也可以指定一个时区名,下面这个例子就指定PST作为时区使用:
TIMESTAMP '2005-05-13 07:15:31.1234 PST'
下面这个INSERT语句向purchases_timestamp_with_tz表中添加两行记录:
INSERT INTO purchases_timestamp_with_tz (
product_id, customer_id, made_on
) VALUES (
1, 1, TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
);

INSERT INTO purchases_timestamp_with_tz (
product_id, customer_id, made_on
) VALUES (
1, 2, TIMESTAMP '2005-05-13 07:15:31.1234 PST'
);
 

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

 SELECT *
FROM purchases_timestamp_with_tz;

PRODUCT_ID  CUSTOMER_ID MADE_ON
----------  ----------- ---------------------------------
1             1  13-MAY-05 07.15.31.1234 AM -07:00
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,该列用来记录顾客在何时购买了产品:

 

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

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

 

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

 

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

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

 SELECT *
FROM purchases_with_local_tz;

PRODUCT_ID  CUSTOMER_ID  MADE_ON
----------  -----------  --------------------------
1             1   13-MAY-05 04.15.30.0000 AM
 

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

警告:

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

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

 

 ALTER SESSION SET TIME_ZONE = 'EST';

Session altered.

SELECT *
FROM purchases_with_local_tz;

PRODUCT_ID CUSTOMER_ID  MADE_ON
---------- -----------  --------------------------
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:

 

 ALTER SESSION SET TIME_ZONE = 'PST';

Session altered.
 

 

-EOF-

 

分享到:
评论

相关推荐

    oracle中日期类型与unix 时间戳的转换

    oracle中日期类型与unix 时间戳的转换, Unix时间戳记是从'1970-01-01 00:00:00'GMT开始的秒数,表现为整数型。 Oracle中的时间是Date型,以下函数提供了两种时间转换的Oracle函数 --unix时间戳与date时间互转 ...

    oracle-java数据类型1

    - `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE` 类型对应 Oracle 的时间戳类型,没有标准的 JDBC 类型,但可以通过 `oracle.jdbc.OracleTypes.TIMESTAMP` 等访问 3. **用户定义的...

    Oracle实用教程_04章_Oracle数据类型和函数[整理].pdf

    Oracle 数据类型和函数 Oracle 数据类型是指一组性质相同的值的集合以及定义于这个值集合上的一组操作的总称。在 Oracle 数据库中,数据类型可以分为字符型、数值型、日期型和其它类型等几类。 Oracle 数据类型...

    oracle数据类型和对应的JDBC类型.docx

    - `TIMESTAMP WITH TIME ZONE` 和 `TIMESTAMP WITH LOCAL TIME ZONE`:Oracle 的带时区的时间戳类型,映射为特定的 Oracle 类型如 `oracle.sql.TIMESTAMPTZ` 和 `oracle.sql.TIMESTAMPLTZ`。 5. **二进制类型**: ...

    mssql2oracle类型对比

    - `timestamp`: 存储时间戳信息,等同于Oracle中的`RAW`类型。 - **Oracle**: - `ROWID`: 用于唯一标识行的位置,SQL Server中没有直接对应的类型,可以考虑使用`identity`列类型。 - `CURRVAL`和`NEXTVAL`: ...

    oracle数据库实现获取时间戳的无参函数

    在Oracle数据库中,时间戳(Timestamp)是一种用来记录精确到秒甚至毫秒的日期和时间数据类型。在处理与时间相关的业务逻辑时,有时我们需要获取当前时间的时间戳,以便进行各种计算或比较。本文将详细介绍如何在...

    kettle实现时间戳增量同步案例

    在这个“kettle实现时间戳增量同步案例”中,我们将探讨如何利用Kettle来实现Oracle数据库中基于时间戳的数据增量同步。 1. **理解时间戳增量同步** 时间戳增量同步是指只同步自上次同步以来发生改变的数据,通常...

    Oracle 10g 操作手册 Oracle数据类型精解

    本操作手册旨在深入解析Oracle 10g的使用,特别是其数据类型的详细信息,同时通过PowerDesigner的实例教程,帮助初学者快速入门并积累实践经验。 一、Oracle数据类型详解 在Oracle数据库中,数据类型定义了列可以...

    oracle_数据类型讲解.ppt

    在Oracle数据库中,数据类型分为多个类别,包括字符类型、数值类型、二进制类型、日期和时间类型以及特殊类型。下面将详细介绍这些数据类型。 1. **字符类型**: - **CHAR**: 定长字符串类型,最多可存储2000字节...

    Oracle数据类型

    日期时间类型适用于时间戳和时间周期的存储,特别是当应用需要处理复杂的时间计算和时区转换时。 总之,Oracle提供了丰富多样的数据类型,以适应各种复杂的应用场景。在数据库设计阶段,了解并合理选择数据类型对于...

    Oracle数据类型说明

    ### Oracle 数据类型详解 #### 一、概述 Oracle 数据库提供了丰富的数据类型,这些类型能够满足各种业务场景的需求。在数据库设计中合理选择数据类型对于提高数据库性能、减少存储空间和确保数据一致性等方面都至...

    mysql与Oracle数据类型比较.pdf

    - `TIMESTAMP`在Oracle中仍为`TIMESTAMP`,用于存储日期和时间戳。 4. **字符串类型**: - MySQL的`CHAR`在Oracle中保留为`CHAR`,用于存储固定长度的字符串。 - `VARCHAR`在Oracle中可以是`VARCHAR2`,两者都...

    Oracle数据类型详解

    在Oracle 10g这个版本中,数据类型是构建数据库结构的基础,它们定义了存储在表中的数据的格式和特性。理解Oracle数据类型对于数据库设计、开发和管理至关重要。以下是Oracle 10g中数据类型的详细讲解: 1. **数值...

    oracle日期时间数据类型与时间间隔数据类型讲解

    - **说明**: 时间戳类型,记录日期和时间,精确到秒后的毫秒级别。 - **格式**: 年-月-日 时:分:秒.毫秒 (YYYY-MM-DD HH:MI:SS.FF) - **参数**: `TIMESTAMP(fractional_seconds_precision)` - `fractional_seconds_...

    sql mysql oracle数据类型

    【标题】: "SQL MySQL Oracle 数据类型对比与理解" 【描述】: "本文将深入探讨 MySQL、Oracle 和 SQL Server 三大主流数据库系统中的数据类型,以及它们与 JDBC 数据类型的对应关系,帮助读者全面理解各数据库的...

    java存储oracle中的clob类型

    在处理日期类型时,需要特别注意Java与Oracle之间的时间戳格式不一致可能导致的问题。 **示例代码:** ```java java.util.Date date = new java.util.Date(); PreparedStatement ps = conn.prepareStatement(...

    asp用的mysql时间戳转换方法

    例如,当我们需要将MySQL中的数据导出到Oracle或SQLServer时,可能会遇到时间戳格式不兼容的情况。本文主要介绍一种在ASP环境下实现MySQL时间戳转换为SQL Server日期格式的方法。 #### MySQL时间戳特点 MySQL存储...

    Oracle如何获取系统当前时间等操作实例

    时间戳在Oracle中通常涉及日期转换。要获取当年一月一日,可以使用`TO_DATE`函数结合字符串拼接,例如: ```sql SELECT TO_DATE(CONCAT((SELECT TO_CHAR(SYSDATE, 'yyyy') FROM DUAL), '-01-01 00:00:00'), 'yyyy-MM...

    Oracle日期类型处理

    ### Oracle日期类型处理知识点 #### 一、Oracle日期类型的存储与表示 在Oracle数据库中,日期类型被用来存储日期和时间信息。一个日期值包括年、月、日、时、分、秒等部分,并且Oracle默认以内部格式存储日期值,...

    使用oracle计算系统当前时间的毫秒数

    #### Java与Oracle时间戳的转换示例 在Java中,可以通过`java.util.Date`类的构造函数或`System.currentTimeMillis()`方法来处理Oracle返回的毫秒数。以下是一个简单的示例代码: ```java Date date = new Date...

Global site tag (gtag.js) - Google Analytics