Oracle中TIMESTAMP的几种类型介绍与实例

本文介绍了Oracle中TIMESTAMP数据类型的几种不同的类型，并通过实例详细解释了它们的使用方法和特性。TIMESTAMP在Oracle中用于存储精确的时间戳信息，对于需要精确记录时间的数据操作非常重要。

Oracle中TIMESTAMP类型全面解析：类型介绍、实例及用法技巧

在Oracle数据库中，TIMESTAMP类型用于存储日期和时间信息，具有很高的精度，可以表示到秒的小数点后9位，TIMESTAMP类型比DATE类型具有更高的时间精度，能够满足更多场景的需求，本文将详细介绍Oracle中TIMESTAMP的几种类型，并通过实例讲解它们的用法。

TIMESTAMP类型介绍

1、TIMESTAMP

TIMESTAMP是最基本的TIMESTAMP类型，它可以表示从公元前4712年1月1日到公元9999年12月31日的任意时间点，精度为秒的小数点后9位。

2、TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE类型在TIMESTAMP的基础上增加了时区信息，可以表示全球任意时区的时间点，该类型在存储时会保存时区信息，查询时可以根据当前会话的时区进行转换。

3、TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE类型与TIMESTAMP WITH TIME ZONE类似，也包含时区信息，不同之处在于，TIMESTAMP WITH LOCAL TIME ZONE在存储时会将时间转换为本地时区，查询时以本地时区显示。

4、INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH类型用于表示一段时间，单位为年和月，可以使用该类型表示5年3个月。

5、INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND类型用于表示一段时间，单位为天、小时、分钟和秒，该类型可以精确到秒的小数点后9位。

实例

1、TIMESTAMP类型的使用

创建一个表，包含TIMESTAMP类型的字段：

CREATE TABLE t_timestamp (    id NUMBER,    ts TIMESTAMP);

插入数据：

INSERT INTO t_timestamp (id, ts) VALUES (1, TIMESTAMP '2021-01-01 12:30:45.6789');

查询数据：

SELECT * FROM t_timestamp;

结果：

ID TS--- ---------------------  1 2021-01-01 12:30:45.6789

2、TIMESTAMP WITH TIME ZONE类型的使用

创建一个表，包含TIMESTAMP WITH TIME ZONE类型的字段：

CREATE TABLE t_timestamp_tz (    id NUMBER,    tstz TIMESTAMP WITH TIME ZONE);

插入数据：

INSERT INTO t_timestamp_tz (id, tstz) VALUES (1, TIMESTAMP '2021-01-01 12:30:45.6789 +08:00');

查询数据：

SELECT * FROM t_timestamp_tz;

结果（根据当前会话的时区进行转换）：

ID TSTZ--- ---------------------  1 2021-01-01 04:30:45.6789

3、TIMESTAMP WITH LOCAL TIME ZONE类型的使用

创建一个表，包含TIMESTAMP WITH LOCAL TIME ZONE类型的字段：

CREATE TABLE t_timestamp_ltz (    id NUMBER,    tsltz TIMESTAMP WITH LOCAL TIME ZONE);

插入数据：

INSERT INTO t_timestamp_ltz (id, tsltz) VALUES (1, TIMESTAMP '2021-01-01 12:30:45.6789 +08:00');

查询数据：

SELECT * FROM t_timestamp_ltz;

结果（以本地时区显示）：

ID TSLTZ--- ---------------------  1 2021-01-01 12:30:45.6789

4、INTERVAL YEAR TO MONTH类型的使用

创建一个表，包含INTERVAL YEAR TO MONTH类型的字段：

CREATE TABLE t_interval_year_to_month (    id NUMBER,    iytm INTERVAL YEAR(5) TO MONTH);

插入数据：

INSERT INTO t_interval_year_to_month (id, iytm) VALUES (1, INTERVAL '5-3' YEAR TO MONTH);

查询数据：

SELECT * FROM t_interval_year_to_month;

结果：

ID IYTM--- ------------  1 +05-03

5、INTERVAL DAY TO SECOND类型的使用

创建一个表，包含INTERVAL DAY TO SECOND类型的字段：

CREATE TABLE t_interval_day_to_second (    id NUMBER,    idts INTERVAL DAY(2) TO SECOND(6));

插入数据：

INSERT INTO t_interval_day_to_second (id, idts) VALUES (1, INTERVAL '10 12:30:45.6789' DAY TO SECOND);

查询数据：

SELECT * FROM t_interval_day_to_second;

结果：

ID IDTS--- -------------------  1 +10 12:30:45.678900

用法技巧

1、TIMESTAMP类型与DATE类型之间的转换

将TIMESTAMP转换为DATE：

SELECT TO_DATE(ts, 'YYYY-MM-DD HH24:MI:SS') FROM t_timestamp;

将DATE转换为TIMESTAMP：

SELECT TO_TIMESTAMP(d, 'YYYY-MM-DD HH24:MI:SS') FROM t_date;

2、TIMESTAMP类型与字符串之间的转换

将TIMESTAMP转换为字符串：

SELECT TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS') FROM t_timestamp;

将字符串转换为TIMESTAMP：

SELECT TO_TIMESTAMP('2021-01-01 12:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

3、使用时区

在插入数据时，可以指定时区：

INSERT INTO t_timestamp_tz (id, tstz) VALUES (2, TIMESTAMP '2021-01-01 12:30:45.6789 AT TIME ZONE 'UTC');

查询时，可以指定时区：

SELECT id, tstz AT TIME ZONE 'UTC' FROM t_timestamp_tz WHERE id = 2;

4、日期时间运算

TIMESTAMP类型支持日期时间运算，

SELECT ts + INTERVAL '1' DAY FROM t_timestamp;

Oracle中的TIMESTAMP类型提供了丰富的日期和时间表示方法，可以满足各种场景的需求，通过本文的介绍和实例，相信读者已经对TIMESTAMP类型有了更深入的了解，可以更好地在Oracle数据库中应用这些类型。