一、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
:将时区信息转换为数据库所在时区进行存储,但查询时会转换为用户会话所在时区。
详细说明
-
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;
- 语法:
-
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
数据类型的时间范围:
-
Oracle:
TIMESTAMP
: 从公元前4712年1月1日到公元9999年12月31日。TIMESTAMP WITH TIME ZONE
和TIMESTAMP WITH LOCAL TIME ZONE
: 同样的时间范围,但带有时区信息。
-
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日。
-
PostgreSQL:
TIMESTAMP
和TIMESTAMP WITH TIME ZONE
: 从4713 BC 到 294276 AD。
-
SQL Server:
DATETIME
: 从1753年1月1日到9999年12月31日。DATETIME2
: 从0001年1月1日到9999年12月31日。
需要注意的是,虽然 TIMESTAMP
和类似的数据类型通常都表示高精度的日期和时间,但其具体范围和行为可能会因数据库系统的不同而有所变化。