sql-date-and-time

SQL 日期和时间数据

日期和时间处理是几乎所有应用程序的基本方面，从记录事件到安排约会。SQL 提供了一套强大的数据类型和函数，用于准确高效地存储、操作和查询时间数据。本教程涵盖了在 SQL 中处理日期和时间的现代最佳实践。

标准 SQL 日期/时间数据类型

SQL 标准定义了几种用于时间值的数据类型。大多数现代数据库系统都支持这些或非常相似的类型。

数据类型	描述	示例
DATE	存储日历日期（年、月、日）。	‘2024-10-26’
TIME	存储一天中的时间（时、分、秒）。	‘14:30:05’
TIMESTAMP	存储日期和时间，不包含时区信息。也称为“朴素”时间戳。	‘2024-10-26 14:30:05.123’
TIMESTAMP WITH TIME ZONE	存储日期、时间及带时区信息。记录特定时间点的最稳健选择。	‘2024-10-26 14:30:05.123-05:00’
INTERVAL	存储一段时间间隔。	‘3 days’, ‘5 hours’, ‘2 years’

关于厂商差异的说明：有些系统有像 DATETIME（SQL Server, MySQL）这样的历史类型。现代最佳实践是在新开发中优先选择标准类型，例如 TIMESTAMP WITH TIME ZONE 或 SQL Server 中的 DATETIME2(n)。

时区至关重要

时区处理不当是软件开发中最常见且代价高昂的错误之一。一个“朴素”的 TIMESTAMP，例如 ‘2024-10-26 09:00:00’，可能意味着纽约、伦敦或东京的上午 9 点——这是三个截然不同的时间点。

最佳实践：以 UTC 存储

行业标准最佳实践是始终以协调世界时 (UTC) 存储时间戳。你的数据库服务器、应用服务器和数据库列都应配置为 UTC。使用 TIMESTAMP WITH TIME ZONE（或等效类型）作为你的数据类型。

转换为用户本地时区应在最后时刻进行，通常在表示层（例如，在 JavaScript 前端或服务器端渲染逻辑中）。

基本日期/时间函数

SQL 提供了获取当前时间、操作日期和提取日期组件的函数。

• 获取当前时间：标准函数是 CURRENT_TIMESTAMP，它返回一个 TIMESTAMP WITH TIME ZONE。CURRENT_DATE 和 CURRENT_TIME 也可用。（注意：NOW() 是许多数据库（如 PostgreSQL 和 MySQL）中常见的非标准等效函数）。
• 提取组件：EXTRACT 函数允许你提取日期的各个部分。EXTRACT(YEAR FROM my_timestamp_column) 将返回年份。
• 日期算术：你可以从时间戳中添加或减去 INTERVAL 值。CURRENT_DATE + INTERVAL '7 day' 将给出从现在起一周后的日期。

函数示例

-- 获取带时区的当前时间（标准 SQL）
SELECT CURRENT_TIMESTAMP;
-- 结果：2024-10-26 15:31:32.123+00

-- 从特定日期获取年份
SELECT EXTRACT(YEAR FROM DATE '2023-08-22');
-- 结果：2023

-- 计算从现在起 3 个月后的日期（PostgreSQL 语法）
SELECT (CURRENT_DATE + INTERVAL '3 month');
-- 结果：3 个月后的日期

实际示例和常见操作

我们来创建一个表来记录用户事件。

CREATE TABLE user_events (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    event_type VARCHAR(50),
    -- 最佳实践：使用 TIMESTAMP WITH TIME ZONE 并默认设置为 UTC 的当前时间
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 插入一个事件（'created_at' 将自动设置）
INSERT INTO user_events (user_id, event_type) VALUES (123, 'LOGIN_SUCCESS');

查询时间范围

查找昨天发生的所有事件：

SELECT * FROM user_events
WHERE created_at >= (CURRENT_DATE - INTERVAL '1 day')
  AND created_at < CURRENT_DATE;

常见陷阱与规避

• 将日期存储为字符串：切勿将日期或时间存储在 VARCHAR 或 TEXT 列中。这会阻止正确的排序、日期算术运算、验证，并且效率极低。
• 忽略时区：对于全球化应用程序，未能使用时区感知的时间戳（TIMESTAMP WITH TIME ZONE）将导致数据不正确。
• 对时间戳使用 BETWEEN：BETWEEN 在两端都是包含的。对于时间戳范围，使用 >= 和 < 的组合更安全、更精确，以避免意外包含下一天的午夜。