SQL Dates

在 SQL 中使用日期和时间

处理日期和时间是 SQL 中的常见任务。主要挑战通常在于理解你的特定数据库系统提供的日期/时间数据类型，以及使用正确的函数来操作和查询它们。

	关键考虑事项：确保你在查询中使用的日期/时间文字值的格式与数据库期望的格式匹配，或使用显式的转换/类型转换函数。

如果你的数据只包含日期部分，查询通常很简单。但是，当涉及时间组件或时区时，查询需要更高的精确度。

常见的 SQL 日期/时间函数（ANSI SQL 和厂商特定）

虽然许多日期/时间函数是特定于数据库的，但有些概念和函数是通用的或属于 SQL 标准：

• CURRENT_DATE: 返回当前日期。
• CURRENT_TIME: 返回当前时间（通常带有时区）。
• CURRENT_TIMESTAMP 或 NOW(): 返回当前日期和时间（通常带有时区）。(NOW() 很常见，但在所有上下文中并非严格遵守 ANSI 标准)。
• EXTRACT(part FROM date_expression): 从日期/时间值中提取特定部分（例如，YEAR, MONTH, DAY, HOUR）。
• 日期算术：添加或减去间隔（例如，date_column + INTERVAL ‘7’ DAY）。间隔的语法各不相同。

以下是特定数据库系统的重要内置日期函数列表：

MySQL 日期/时间函数：

函数	描述
NOW()	返回当前日期和时间。
CURDATE()	返回当前日期。
CURTIME()	返回当前时间。
DATE(expr)	提取日期或 datetime 表达式的日期部分。
TIME(expr)	提取 datetime 表达式的时间部分。
EXTRACT(unit FROM date)	返回日期/时间的单个部分（例如，YEAR, MONTH, DAY）。
DATE_ADD(date, INTERVAL expr unit)	向日期添加指定的时间间隔。
DATE_SUB(date, INTERVAL expr unit)	从日期减去指定的时间间隔。
DATEDIFF(expr1, expr2)	返回两个日期之间的天数。
DATE_FORMAT(date, format)	根据格式字符串格式化日期/时间值。

SQL Server 日期/时间函数：

函数	描述
GETDATE() / SYSDATETIME()	返回当前日期和时间（SYSDATETIME() 提供更高的精确度）。
DATEPART(datepart, date)	以整数形式返回日期/时间的单个部分。
DATENAME(datepart, date)	以字符串形式返回日期/时间的单个部分。
DATEADD(datepart, number, date)	向日期添加指定的时间间隔。
DATEDIFF(datepart, startdate, enddate)	返回两个日期之间的时差，单位由 datepart 指定。
CONVERT(datatype, expression, [style])	将表达式从一种数据类型转换为另一种，常用于日期格式化。
FORMAT(date, format, [culture])	格式化日期/时间值（SQL Server 2012+）。

SQL 日期/时间数据类型

为日期和时间信息选择正确的数据类型对于存储效率和查询性能至关重要。

MySQL 数据类型：

• DATE: 以 ‘YYYY-MM-DD’ 格式存储日期值。
• DATETIME: 以 ‘YYYY-MM-DD HH:MI:SS’ 格式存储日期和时间值。范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
• TIMESTAMP: 存储日期和时间值。范围从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。值以 UTC 存储，并根据会话时区进行转换。可在行修改时自动更新。
• TIME: 以 ‘HH:MI:SS’ 格式存储时间值。
• YEAR: 以 YYYY 或 YY 格式存储年份值。

SQL Server 数据类型：

• DATE: 以 YYYY-MM-DD 格式存储日期值。
• TIME: 以 hh:mm:ss.nnnnnnn 格式存储时间值。
• DATETIME2(n): 存储日期和时间，带小数秒精度 (n=0 到 7)。优于旧的 DATETIME。格式：YYYY-MM-DD hh:mm:ss.nnnnnnn。
• SMALLDATETIME: 存储日期和时间，精度较低（无小数秒，秒被四舍五入）。格式：YYYY-MM-DD HH:MI:SS。
• DATETIME: 旧的日期和时间类型。比 DATETIME2 精度低、范围小。
• DATETIMEOFFSET(n): 存储带时区意识的日期和时间。
• TIMESTAMP: 注意：在 SQL Server 中，TIMESTAMP（或 ROWVERSION）不是日期/时间类型，而是一个唯一的二进制数，在行修改时更新，用于版本控制。

请务必查阅你的数据库系统的文档，以获取关于数据类型和函数的最准确和详细信息。

查询日期和时间值

	比较不带时间组件的日期通常很简单。包含时间组件则需要仔细处理。

假设我们有一个名为 “Orders” 的表，其中包含一个 OrderDate 列。

场景 1: OrderDate 列的数据类型为 DATE（只存储日期部分）：

OrderId ProductName OrderDate

1 Geitost 2023-11-11

2 Camembert Pierrot 2023-11-09

3 Mozzarella di Giovanni 2023-11-11

选择 ‘2023-11-11’ 下单的订单：

SELECT * FROM Orders WHERE OrderDate = ‘2023-11-11’;

此查询按预期工作，返回订单 1 和 3。

场景 2: OrderDate 列的数据类型为 DATETIME 或 TIMESTAMP（存储日期和时间）：

OrderId ProductName OrderDate

1 Geitost 2023-11-11 13:23:44

2 Camembert Pierrot 2023-11-09 15:45:21

3 Mozzarella di Giovanni 2023-11-11 11:12:01

4 Mascarpone Fabioli 2023-10-29 14:56:59

如果使用上面相同的查询：

SELECT * FROM Orders WHERE OrderDate = ‘2023-11-11’;

此查询很可能不会返回结果（或根据隐式转换表现出意外行为）。这是因为 ‘2023-11-11’ 可能被解释为 ‘2023-11-11 00:00:00’，这与任何 OrderDate 值都不完全匹配。

要正确查询 ‘2023-11-11’ 的所有订单，无论时间如何：

方法 1: 使用日期范围（最可靠且通常对索引友好）：

SELECT * FROM Orders WHERE OrderDate >= ‘2023-11-11 00:00:00’ AND OrderDate < ‘2023-11-12 00:00:00’; — 截止到（但不包含）下一天

方法 2: 将 OrderDate 列转换为 DATE 类型（函数可用性和性能可能有所不同）：

— SQL Server 示例 SELECT * FROM Orders WHERE CAST(OrderDate AS DATE) = ‘2023-11-11’;

— MySQL 示例 SELECT * FROM Orders WHERE DATE(OrderDate) = ‘2023-11-11’;

提示：理解你的日期/时间数据类型，并在查询日期/时间列时始终保持明确，尤其是在涉及时间组件时。使用范围或转换为 DATE 是常见的最佳实践。