Oracle中 DATE 和 TIMESTAMP 比较

一、DATE和TIMESTAMP比较

该表格用于说明DATE和TIMESTAMP数据类型的特点、存储大小、用途、内置函数、转换函数、时区处理，以及插入和查询数据的示例。

特性	DATE	TIMESTAMP
精度	秒 (年月日时分秒)	秒及小数秒（年月日时分秒毫秒，最高9位小数秒）
存储大小	7字节	11字节（默认精度）
用途	一般用途日期和时间存储	需要更高精度的时间戳记录
内置函数	SYSDATE	CURRENT_TIMESTAMP、SYSTIMESTAMP
转换函数	TO_DATE	TO_TIMESTAMP
时区处理	不包含时区信息	TIMESTAMP WITH TIME ZONE支持时区信息，并随时区变化
示例插入数据	TO_DATE('2024-01-01 15:30:45', 'YYYY-MM-DD HH24:MI:SS')	TO_TIMESTAMP('2024-01-01 15:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF')
示例查询数据	SELECT TO_CHAR(date_column, 'YYYY-MM-DD HH24:MI:SS') FROM example_date;	SELECT TO_CHAR(timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FF') FROM example_timestamp;

示例表结构及插入数据

DATE类型

创建表：

CREATE TABLE example_date (
    date_column DATE
);

插入数据：

INSERT INTO example_date (date_column) VALUES (TO_DATE('2024-08-16 15:30:45', 'YYYY-MM-DD HH24:MI:SS'));

查询数据：

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

TIMESTAMP类型

创建表：

CREATE TABLE example_timestamp (
    timestamp_column TIMESTAMP
);

插入数据：

INSERT INTO example_timestamp (timestamp_column) VALUES (TO_TIMESTAMP('2024-08-16 15:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF'));

查询数据：

SELECT TO_CHAR(timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FF') FROM example_timestamp;

二、TIMESTAMP时区方面说明

关于TIMESTAMP数据类型时区方面的内容，主要包括以下几个方面：

TIMESTAMP 与 时区

• TIMESTAMP：仅包含日期和时间，不包含时区信息。
• TIMESTAMP WITH TIME ZONE：包含日期、时间及时区信息。它不仅记录了具体的时间，还记录了对应的时区。
• TIMESTAMP WITH LOCAL TIME ZONE：将时区信息转换为数据库所在时区进行存储，但查询时会转换为用户会话所在时区。

详细说明

1. TIMESTAMP WITH TIME ZONE：

   • 语法：TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
   • 存储的是绝对时间，与时区相关。
   • 插入示例：

     INSERT INTO example_tz (tz_column) VALUES (TIMESTAMP '2024-08-16 15:30:45.123456 +02:00');
     

   • 查询时会以时区形式显示，支持转换时区显示：

     SELECT tz_column AT TIME ZONE 'UTC' FROM example_tz;
     

2. TIMESTAMP WITH LOCAL TIME ZONE：

   • 语法：类似于 TIMESTAMP WITH TIME ZONE，但存储时不记录时区信息，内部转换为数据库所在时区。
   • 存储在数据库中时，时区被转换为数据库时区，查询时再转换为会话时区。
   • 插入示例：

     INSERT INTO example_ltz (ltz_column) VALUES (TIMESTAMP '2024-08-16 15:30:45.123456 +02:00');
     

   • 查询时会自动转换为当前会话时区：

     SELECT ltz_column FROM example_ltz;
     

示例表结构及插入数据

TIMESTAMP WITH TIME ZONE类型

创建表：

CREATE TABLE example_tz (
    tz_column TIMESTAMP WITH TIME ZONE
);

插入数据：

INSERT INTO example_tz (tz_column) VALUES (TIMESTAMP '2024-08-16 15:30:45.123456 +02:00');

查询数据：

SELECT tz_column AT TIME ZONE 'UTC' FROM example_tz;

TIMESTAMP WITH LOCAL TIME ZONE类型

创建表：

CREATE TABLE example_ltz (
    ltz_column TIMESTAMP WITH LOCAL TIME ZONE
);

插入数据：

INSERT INTO example_ltz (ltz_column) VALUES (TIMESTAMP '2024-08-16 15:30:45.123456 +02:00');

查询数据：

SELECT ltz_column FROM example_ltz;

三、TIMESTAMP时间范围说明

TIMESTAMP 数据类型的时间范围取决于数据库管理系统（DBMS）的具体实现。以下是几种常见数据库系统中 TIMESTAMP 数据类型的时间范围：

1. Oracle:

   • TIMESTAMP: 从公元前4712年1月1日到公元9999年12月31日。
   • TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE: 同样的时间范围，但带有时区信息。

2. MySQL:

   • TIMESTAMP: 从1970年1月1日 00:00:01 UTC 到 2038年1月19日 03:14:07 UTC（32位 Unix 时间戳的限制）。
   • DATETIME: 从1000年1月1日到9999年12月31日。

3. PostgreSQL:

   • TIMESTAMP 和 TIMESTAMP WITH TIME ZONE: 从4713 BC 到 294276 AD。

4. SQL Server:

   • DATETIME: 从1753年1月1日到9999年12月31日。
   • DATETIME2: 从0001年1月1日到9999年12月31日。

需要注意的是，虽然 TIMESTAMP 和类似的数据类型通常都表示高精度的日期和时间，但其具体范围和行为可能会因数据库系统的不同而有所变化。