SQL 日期与时间数据类型

Begindev 编辑部 March 31, 2026

在 PostgreSQL 等关系型数据库中，提供了一套丰富的数据类型来处理日期和时间信息，这对于有效管理时间序列数据至关重要。选择正确的数据类型是确保数据完整性、提高存储效率以及进行精确时间计算的关键。

本章将深入探讨 DATE、TIME 和 TIMESTAMP 这三种核心数据类型，全面讲解它们的用法、细微差别以及实际应用场景。理解这些数据类型不仅对存储时间数据必不可少，更是对数据进行过滤、排序和执行计算的基础。这些能力是构建数据分析报表、日程安排应用程序、系统审计日志以及许多其他依赖时间信息系统的基础。

1. DATE 数据类型 (日期)

DATE 数据类型用于存储日历日期，不包含任何一天中的时间组件（即没有时、分、秒）。它代表公历中的某一个特定日子。在数据库底层，DATE 值通常被表示为一个整数，代表从某个特定纪元（例如 2000 年 1 月 1 日）起经过的天数。

1.1 语法与用法

要将列声明为 DATE 类型，请使用以下语法：

column_name DATE;

要插入日期值，请使用标准的 YYYY-MM-DD（年-月-日）格式：

INSERT INTO events (event_date, event_name) VALUES ('2024-03-15', '技术研讨会');

1.2 基础示例

让我们创建一个记录生日的表：

CREATE TABLE birthdays (
    person_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE
);

INSERT INTO birthdays (name, birth_date) VALUES ('Alice', '1990-05-20');
INSERT INTO birthdays (name, birth_date) VALUES ('Bob', '1985-12-10');

SELECT * FROM birthdays;

这将会输出：

person_id | name  | birth_date 
-----------+-------+------------
         1 | Alice | 1990-05-20
         2 | Bob   | 1985-12-10
(2 rows)

1.3 进阶示例：使用日期函数

PostgreSQL 提供了许多内置函数来处理日期。例如，你可以使用 EXTRACT() 函数从日期中提取特定的年份、月份或日期：

SELECT name, EXTRACT(YEAR FROM birth_date) AS birth_year FROM birthdays;

输出结果：

name  | birth_year
-------+------------
 Alice |       1990
 Bob   |       1985
(2 rows)

你也可以使用 AGE() 函数计算一个人的年龄（它会计算指定日期与当前系统日期之间的时间间隔）：

SELECT name, AGE(birth_date) AS age FROM birthdays;

这将会输出（假设当前日期是 2024-10-27）：

name  |    age
-------+----------
 Alice | 34 years
 Bob   | 38 years
(2 rows)

1.4 实际应用注意事项

时区 (Time Zones)： DATE 数据类型不存储时区信息。它仅仅代表日历上的某一天。
日期格式： 虽然标准格式是 YYYY-MM-DD，但 PostgreSQL 通常很灵活，可以自动转换其他格式的日期。不过，最佳实践是始终坚持使用标准格式以避免产生歧义。
有效范围： DATE 数据类型的有效范围通常是从公元前 4713 年 1 月 1 日到公元 5874897 年 12 月 31 日。

2. TIME 数据类型 (时间)

TIME 数据类型用于存储一天中的特定时间，不包含任何日期组件（即没有年、月、日）。它包含小时、分钟和秒，并且可以选择包含小数秒（毫秒/微秒）。

2.1 语法与用法

要将列声明为 TIME 类型，请使用以下语法：

column_name TIME;

要插入时间值，请使用 HH:MI:SS（时:分:秒）格式。你也可以包含小数秒，例如 HH:MI:SS.MS。

INSERT INTO schedules (task, start_time) VALUES ('部门会议', '10:00:00');

2.2 基础示例

CREATE TABLE schedules (
    task_id SERIAL PRIMARY KEY,
    task VARCHAR(100),
    start_time TIME
);

INSERT INTO schedules (task, start_time) VALUES ('晨间站会', '09:00:00');
INSERT INTO schedules (task, start_time) VALUES ('午休时间', '12:30:00');

SELECT * FROM schedules;

输出结果：

task_id |     task      | start_time
---------+---------------+------------
       1 | 晨间站会      | 09:00:00
       2 | 午休时间      | 12:30:00
(2 rows)

2.3 进阶示例：带时区的 TIME

PostgreSQL 还支持 TIME WITH TIME ZONE 类型，它会将时间与时区偏移量一起存储。

CREATE TABLE time_zones (
    event VARCHAR(100),
    event_time TIME WITH TIME ZONE
);

INSERT INTO time_zones (event, event_time) VALUES ('纽约会议', '14:00:00-05');  -- 纽约时间下午 2 点 (EST)
INSERT INTO time_zones (event, event_time) VALUES ('伦敦会议', '19:00:00+01');  -- 伦敦时间晚上 7 点 (BST)

SELECT * FROM time_zones;

输出结果：

event         |  event_time
----------------------+-------------------
 纽约会议             | 14:00:00-05
 伦敦会议             | 19:00:00+01
(2 rows)

2.4 实际应用注意事项

时区：默认的 TIME 数据类型不存储时区信息。如果你需要记录特定的时区，请明确使用 TIME WITH TIME ZONE。
小数秒 (Fractional Seconds)：你可以使用 TIME(p) 指定小数秒的精度，其中 p 是小数位数。例如，TIME(3) 可以存储精确到毫秒的时间。
有效范围： TIME 数据类型的有效范围是 00:00:00 到 24:00:00。请注意，24:00:00 可以用来表示一天的结束（午夜）。

3. TIMESTAMP 数据类型 (时间戳)

TIMESTAMP 数据类型同时存储日期和时间信息。它实际上是 DATE 和 TIME 数据类型功能的结合。TIMESTAMP 有两个主要的变体：TIMESTAMP WITHOUT TIME ZONE（不带时区的时间戳）和 TIMESTAMP WITH TIME ZONE（带时区的时间戳）。

3.1 语法与用法

要将列声明为 TIMESTAMP 类型，请使用以下语法：

column_name TIMESTAMP;  -- 默认为不带时区 TIMESTAMP WITHOUT TIME ZONE
-- 或者
column_name TIMESTAMP WITH TIME ZONE;

插入时间戳值时，请使用 YYYY-MM-DD HH:MI:SS 格式。你也可以包含小数秒。对于带时区的时间戳，需在末尾加上时区偏移量。

INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:30:00', '用户已登录');
INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:30:00+02', '用户已登录'); -- 带时区的写法

3.2 基础示例：不带时区的 TIMESTAMP

CREATE TABLE audit_log (
    log_id SERIAL PRIMARY KEY,
    event_time TIMESTAMP,
    description TEXT
);

INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:30:00', '用户已登录');
INSERT INTO audit_log (event_time, description) VALUES ('2024-03-15 10:45:00', '数据已更新');

SELECT * FROM audit_log;

输出结果：

log_id |      event_time       | description
--------+---------------------+-------------
      1 | 2024-03-15 10:30:00 | 用户已登录
      2 | 2024-03-15 10:45:00 | 数据已更新
(2 rows)

3.3 进阶示例：带时区的 TIMESTAMP

CREATE TABLE meetings (
    meeting_id SERIAL PRIMARY KEY,
    start_time TIMESTAMP WITH TIME ZONE,
    description TEXT
);

INSERT INTO meetings (start_time, description) VALUES ('2024-03-15 14:00:00-05', '纽约团队同步会');  -- 纽约时间 (EST)
INSERT INTO meetings (start_time, description) VALUES ('2024-03-15 19:00:00+01', '伦敦团队同步会');  -- 伦敦时间 (BST)

SELECT * FROM meetings;

输出结果（假设你的数据库服务器系统时区设置为 UTC）：

meeting_id |         start_time          |    description
------------+----------------------------+---------------------
          1 | 2024-03-15 19:00:00+00     | 纽约团队同步会
          2 | 2024-03-15 18:00:00+00     | 伦敦团队同步会
(2 rows)

关键提示： 请注意，PostgreSQL 会在底层将所有 TIMESTAMP WITH TIME ZONE 的值转换为 UTC (协调世界时) 进行统一存储（上面的 +00 代表 UTC）。当查询时，它会根据你当前数据库连接的时区设置进行转换后显示。

3.4 实际应用注意事项

时区处理的核心区别： TIMESTAMP 和 TIMESTAMP WITH TIME ZONE 之间最关键的区别在于它们如何处理时区信息：

TIMESTAMP WITHOUT TIME ZONE：原封不动地存储你输入的日期和时间，不进行任何时区转换。它假定你所有的业务时间戳都处于同一个默认时区。
TIMESTAMP WITH TIME ZONE：接收带时区信息的时间，并在内部统一转换为 UTC 存储。在检索数据时，它会自动将 UTC 时间转换为当前数据库客户端设置的时区。

如何选择合适的类型：

当你想原样记录一个确切的本地时间点，并且你的应用只服务于单一时区（或者你完全不在乎跨时区问题）时，使用 不带时区 的类型。
当你的应用程序面向全球用户，需要处理来自不同时区的时间，并且必须准确保留时间发生的绝对时刻时，强烈建议使用带时区 的类型。

小数精度： 你可以使用 TIMESTAMP(p) 指定精度。例如，TIMESTAMP(6) 存储具有微秒精度的时间戳。