SQL Server DATEDIFF：计算时间间隔入门
作者：社区 / 用例
2023 年 2 月 9 日
导航至
大家好！您是否曾经需要在 SQL 数据库中计算两个日期之间的差异？如果是这样，我有个好消息要告诉您：SQL Server 有一个很棒的内置函数叫做 DATEDIFF，它就是做这个的。在这篇文章中，我们将探讨 DATEDIFF 是什么、它是如何工作的，以及何时以及为何使用它。我们还将深入探讨一些实际示例，例如查找日期与今天之间的天数，以及获取两个列之间的日期差。
那么，让我们开始吧！
DATEDIFF：它是什么？
DATEDIFF 是一个强大的 SQL Server 函数，用于计算两个日期或日期时间之间的差异，并将结果作为整数返回。当您需要查找某物的年龄时，例如用户帐户已存在多少天，或者两个事件之间有多少个月时，它非常有用。
DATEDIFF：语法和参数
DATEDIFF 的语法非常简单
DATEDIFF(datepart, startdate, enddate)让我们探讨一下这里使用的参数
-
datepart: 您想要用于计算的时间单位，例如年、季度、月、日，甚至更小的单位，如小时、分钟或秒。
-
startdate: 第一个日期或日期时间值。
-
enddate: 第二个日期或日期时间值。
请记住，DATEDIFF 只返回正整数，因此如果结束日期早于开始日期，您将得到负数结果。
DATEDIFF 的工作原理
使用 DATEDIFF 时，了解其行为至关重要。DATEDIFF 计算开始日期和结束日期之间跨越的指定 datepart 边界的数量。但是，它不计算两个日期之间 datepart 单位的数量。相反，它查看单位之间的边界。例如，考虑以下查询
SELECT DATEDIFF(month, '2022-01-29', '2022-02-01');尽管两个日期之间只有三天，但结果为 1，因为月份边界（从一月到二月）被跨越了一次。
DATEDIFF 精度
DATEDIFF 返回一个整数，这可能会导致截断或舍入问题。例如，如果您计算两个日期之间的月份差，并且结果不是整数，DATEDIFF 将截断小数部分。例如
SELECT DATEDIFF(month, '2022-01-15', '2022-02-14');实际差异约为 0.97 个月，但 DATEDIFF 返回 0，因为月份边界未被跨越。为了获得更高的精度，您可以使用更小的 datepart，并将结果转换为您想要的单位。例如，您可以计算天数差，然后将其除以一个月中的平均天数
SELECT DATEDIFF(day, '2022-01-15', '2022-02-14') / 30.44;请记住，这只是一个近似值。
DATEDIFF 和边界值
了解 DATEDIFF 如何处理边界值对于正确的计算至关重要。例如，让我们计算 2021 年 12 月 31 日和 2022 年 1 月 1 日之间的年份差
SELECT DATEDIFF(year, '2021-12-31', '2022-01-01');尽管日期之间只有一天的间隔，但结果为 1，因为年份边界被跨越了。同样，计算 2022 年 1 月 31 日和 2022 年 2 月 1 日之间的月份差将得到 1，尽管只有一天的间隔。根据您的具体要求，考虑这些边界情况并相应地调整您的计算至关重要。
何时以及为何使用 DATEDIFF
您可能想知道在实际场景中何时实际使用 DATEDIFF。让我们看一些例子
-
计算用户或产品的天数、月数或年数。
-
查找直到事件或截止日期的天数。
-
分析两个事件之间的时间，例如用户登录或购买。
现在我们已经介绍了基础知识，让我们看一些实际示例。
示例 1：计算日期和今天之间的天数
假设您想找出自用户注册以来经过了多少天。以下是如何使用 DATEDIFF 来做到这一点
SELECT DATEDIFF(day, signup_date, GETDATE()) AS days_since_signup
FROM users;在本例中，我们计算了注册日期和当前日期（使用 GETDATE() 函数）之间的天数差。
示例 2：获取两个列之间的日期差
假设您有一个包含两个日期列的表，start_date 和 end_date，并且您想计算它们之间的天数。以下是如何操作
SELECT DATEDIFF(day, start_date, end_date) AS duration
FROM events;在这种情况下，我们正在查找 start_date 和 end_date 列之间的天数差。
SELECT DATEDIFF
有时，您可能想要根据 DATEDIFF 的输出过滤查询结果。例如，假设您只想显示持续时间超过七天的事件
SELECT *
FROM events
WHERE DATEDIFF(day, start_date, end_date) > 7;在这个查询中，我们在 WHERE 子句中使用 DATEDIFF 来根据 start_date 和 end_date 列之间的差异过滤结果。
DATEADD：配套函数
SQL Server 中另一个有用的函数是 DATEADD，它允许您从给定的日期或日期时间值添加或减去指定的时间间隔。语法如下
DATEADD(datepart, number, date)让我们了解一下参数
-
datepart: 您想要添加或减去的时间单位，例如年、季度、月、日、小时、分钟或秒。
-
number: 要添加或减去的单位数。使用正整数添加时间，使用负整数减去时间。
-
date: 您想要修改的日期或日期时间值。
例如，如果您想查找用户注册日期后 30 天的日期，可以使用以下方法
SELECT DATEADD(day, 30, signup_date) AS thirty_days_later
FROM users;
Snowflake、MySQL 和 PostgreSQL 中的 DATEDIFF
虽然 DATEDIFF 是 SQL Server 特有的，但在其他数据库系统中也有类似的函数
Snowflake
Snowflake 的 DATEDIFF 函数与 SQL Server 具有相同的语法
SELECT DATEDIFF(datepart, startdate, enddate)
FROM table_name;但是，支持的 datepart 值存在一些差异。例如，Snowflake 支持以下值：YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND、MILLISECOND、MICROSECOND 和 NANOSECOND。例如，要计算两个日期之间的周数差，您将使用
SELECT DATEDIFF(week, '2022-01-01', '2022-01-15')
FROM table_name;MySQL
MySQL 使用 TIMESTAMPDIFF 函数来计算两个日期或日期时间之间的差异
SELECT TIMESTAMPDIFF(unit, startdate, enddate)
FROM table_name;其中 unit 表示时间单位，如 YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE 或 SECOND。请注意，TIMESTAMPDIFF 返回一个有符号整数值，并且 startdate 和 enddate 参数的顺序很重要。因此，如果您交换顺序，结果将具有相反的符号。以下是计算两个日期之间小时差的示例
SELECT TIMESTAMPDIFF(HOUR, '2022-01-01 08:00:00', '2022-01-01 12:00:00')
FROM table_name;PostgreSQL
PostgreSQL 允许您通过将一个日期或时间戳减去另一个日期或时间戳来直接计算两个日期或时间戳之间的差异
SELECT end_date - start_date
FROM table_name;此减法返回一个 interval 值。要从 interval 中提取特定单位，您可以使用 EXTRACT 函数
SELECT EXTRACT(unit FROM interval)
FROM table_name;
Here's an example of calculating the difference between two dates in months:
SELECT EXTRACT(MONTH FROM (end_date - start_date))
FROM table_name;重要的是要注意，当使用 EXTRACT 时，结果可能不是整数，尤其是在计算月份或年份差时。但是，您可以使用 ::integer 类型转换或 floor() 函数将结果转换为整数。
总结
就此结束！您现在了解了 DATEDIFF 的基础知识、其语法和参数以及它的工作原理。我们还看了一些实际示例，例如计算日期和今天之间的天数，以及获取两个列之间的日期差。此外，我们还介绍了 SELECT DATEDIFF、DATEADD 以及 Snowflake、MySQL 和 PostgreSQL 中的等效函数。
掌握了这些知识，您现在可以自信地使用 DATEDIFF 及其对应函数来分析和操作 SQL 查询中的日期和时间间隔。
查询愉快！
关于作者
这篇文章由 Juan Reyes 撰写。作为一名企业家、技术精湛的工程师和心理健康倡导者，Juan 追求可持续的自我成长，体现了领导力、智慧和热情。凭借在科技行业超过 15 年的经验，Juan 有机会与日本和美国移动开发、Web 开发和电子商务领域的一些最杰出的参与者合作。
