sql-date-functions

SQL 日期和时间函数

SQL 提供了一套丰富的内置函数来处理日期和时间数据。这些函数允许你执行诸如获取当前时间、计算日期差、提取日期部分以及格式化日期以供显示等操作。对于几乎所有处理时间敏感数据（如日志记录、调度或财务记录）的应用程序来说，理解这些函数都至关重要。

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

在深入了解函数之前，了解用于存储日期和时间信息的常见数据类型非常重要：

• DATE: 仅存储日期（例如，‘2023-10-27’）。
• TIME: 仅存储一天中的时间（例如，‘15:30:00’）。
• TIMESTAMP: 存储日期和时间（例如，‘2023-10-27 15:30:00’）。
• TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ): 存储带有时区信息的日期时间戳。这对于全球性应用程序至关重要。

常用日期和时间函数

尽管具体函数名称在不同的 SQL 方言（如 PostgreSQL、MySQL 和 SQL Server）之间可能有所不同，但核心概念是通用的。以下是一些最重要的函数，并附有方言特定的语法说明。

功能	标准 SQL / 常用名称	描述与示例
获取当前时间	CURRENT_TIMESTAMP, NOW()	返回数据库服务器的当前日期和时间。NOW() 是一个流行的同义词。GETDATE() 专门用于 SQL Server。
获取当前日期	CURRENT_DATE	返回数据库服务器的当前日期。
获取当前时间	CURRENT_TIME	返回数据库服务器的当前时间。
提取日期的部分	EXTRACT(part FROM date)	从日期/时间值中提取组件（如 YEAR, MONTH, DAY, HOUR）的 ANSI SQL 标准。 SELECT EXTRACT(YEAR FROM '2023-10-27'); 返回 2023。
日期/时间算术	INTERVAL	用于添加或减去一段时间。语法略有不同。 PostgreSQL/MySQL: SELECT NOW() + INTERVAL '1 day'; SQL Server: SELECT DATEADD(day, 1, GETDATE());
计算差异	DATEDIFF(part, start_date, end_date)	以指定单位（日、月、年）计算两个日期之间的差异。函数名称和参数顺序可能有所不同。例如，在 PostgreSQL 中，你可以简单地减去时间戳以获得一个 interval。
将日期格式化为字符串	TO_CHAR() / DATE_FORMAT() / FORMAT()	将日期/时间值转换为指定格式的字符串。 PostgreSQL: TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') MySQL: DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') SQL Server: FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')
获取月份的最后一天	EOMONTH(date)	返回给定日期的月份的最后一天。广泛支持，尤其是在 SQL Server 和 PostgreSQL 中（通过函数或日期算术）。
截断日期	DATE_TRUNC(‘part’, date)	将日期/时间值截断到指定的精度（例如，到月份、小时或一天的开始）。在 PostgreSQL 中非常常见。 SELECT DATE_TRUNC('month', NOW()); 将返回当前月份的第一天午夜。
构造日期/时间	MAKE_DATE() / DATEFROMPARTS()	从单个部分（年、月、日等）创建日期或时间戳。SQL Server 中使用 DATEFROMPARTS，而 PostgreSQL 有 MAKE_DATE 和 MAKE_TIMESTAMP 等函数。

实践示例：用户活动分析

假设你有一个 user_logins 表，你想找出每个月有多少用户登录。

CREATE TABLE user_logins (
    user_id INT,
    login_time TIMESTAMP WITH TIME ZONE NOT NULL
);

-- 示例数据
INSERT INTO user_logins (user_id, login_time) VALUES
(1, '2023-01-15 10:00:00Z'),
(2, '2023-01-20 12:30:00Z'),
(1, '2023-02-05 11:00:00Z');

-- PostgreSQL / 标准 SQL 示例
SELECT
    DATE_TRUNC('month', login_time)::DATE AS login_month,
    COUNT(DISTINCT user_id) AS unique_users
FROM
    user_logins
GROUP BY
    login_month
ORDER BY
    login_month;

此查询首先将 login_time 截断到该月的月初，然后按此截断值进行分组，以计算该月的独立用户数。这是数据分析中的常见模式。