PostgreSQL的日期时间差DATEDIFF实例详解

总览

PostgreSQL 不提供类似于 SQL ServerDATEDIFF 的[2]DATEDIFF 函数，但是您可以使用各种表达式或 UDF 来获得相同的结果。

SQL ServerandSybasePostgreSQLYearsDATEDIFF(yy, start, end)DATE_PART('year', end) – DATE_PART('year', start)MonthsDATEDIFF(mm, start, end)years_diff * 12 + (DATE_PART('month', end) – DATE_PART('month', start))DaysDATEDIFF(dd, start, end)DATE_PART('day', end – start)WeeksDATEDIFF(wk, start, end)TRUNC(DATE_PART('day', end – start)/7)HoursDATEDIFF(hh, start, end)days_diff * 24 + DATE_PART('hour', end – start )MinutesDATEDIFF(mi, start, end)hours_diff * 60 + DATE_PART('minute', end – start )SecondsDATEDIFF(ss, start, end)minutes_diff * 60 + DATE_PART('minute', end – start )

PostgreSQL-年中的日期差异

考虑使用 SQL Server 函数来计算以年为单位的两个日期之间的差：

SQL Server：

— Difference between Oct 02, 2011 and Jan 01, 2012 in years
SELECT DATEDIFF(year, \’2011-10-02\’, \’2012-01-01\’);
— Result: 1

请注意，SQL Server DATEDIFF 函数返回 1 年，尽管日期之间只有 3 个月。

SQL Server 不计算日期之间经过的整年，它仅计算年份之间的差异。

在 PostgreSQL 中，您可以从日期中获取年份部分并将其减去。

PostgreSQL：

— Difference between Oct 02, 2011 and Jan 01, 2012 in years
SELECT DATE_PART(\’year\’, \’2012-01-01\’::date) – DATE_PART(\’year\’, \’2011-10-02\’::date);
— Result: 1

PostgreSQL-月中的日期差异

考虑使用 SQL Server 函数来计算两个日期（以月为单位）之间的差额：

SQL Server：

— Difference between Oct 02, 2011 and Jan 01, 2012 in months
SELECT DATEDIFF(month, \’2011-10-02\’, \’2012-01-01\’);
— Result: 3

在 PostgreSQL 中，您可以将年份之间的差值乘以 12，然后将月份部分之间的差值相加（可以为负）。

PostgreSQL：

— Difference between Oct 02, 2011 and Jan 01, 2012 in months
SELECT (DATE_PART(\’year\’, \’2012-01-01\’::date) – DATE_PART(\’year\’, \’2011-10-02\’::date)) * 12 +
(DATE_PART(\’month\’, \’2012-01-01\’::date) – DATE_PART(\’month\’, \’2011-10-02\’::date));
— Result: 3

PostgreSQL-日期的天数差异

考虑使用 SQL Server 函数来计算两天之间的日期差：

SQL Server：

— Difference between Dec 29, 2011 23:00 and Dec 31, 2011 01:00 in days
SELECT DATEDIFF(day, \’2011-12-29 23:00:00\’, \’2011-12-31 01:00:00\’);
— Result: 2

请注意，DATEDIFF 返回了 2 天，尽管 datetime 值之间只有 1 天 2 小时。

在 PostgreSQL 中，如果您从另一个中减去一个日期时间值（TIMESTAMP，DATE 或 TIME 数据类型），则将获得一个 INTERVAL 值，格式为“ddddayshh：mi：ss”。

SELECT \’2011-12-31 01:00:00\’::timestamp – \’2011-12-29 23:00:00\’::timestamp;
— Result: \”1 day 02:00:00\”

SELECT \’2011-12-31 01:00:00\’::timestamp – \’2010-09-17 23:00:00\’::timestamp;
— Result: \”469 days 02:00:00\”

所以，你可以使用 date_part 数函数 extact 的天数，但它返回的数量充分的日期之间的天数。

PostgreSQL：

— Difference between Dec 29, 2011 23:00 and Dec 31, 2011 01:00 in days
SELECT DATE_PART(\’day\’, \’2011-12-31 01:00:00\’::timestamp – \’2011-12-29 23:00:00\’::timestamp);
— Result: 1

PostgreSQL-周中的日期差异

考虑使用 SQL Server 函数来计算两周中两个日期之间的差额：

SQL Server：

— Difference between Dec 22, 2011 and Dec 31, 2011 in weeks
SELECT DATEDIFF(week, \’2011-12-22\’, \’2011-12-31\’);
— Result: 1

DATEDIFF 返回日期时间值之间的整周数。

在 PostgreSQL 中，您可以使用表达式定义天数（请参见上文）并将其除以 7。需要 TRUNC 才能删除除后的小数部分。

PostgreSQL：

— Difference between Dec 22, 2011 and Dec 31, 2011 in weeks
SELECT TRUNC(DATE_PART(\’day\’, \’2011-12-31\’::timestamp – \’2011-12-22\’::timestamp)/7);
— Result: 1

PostgreSQL-日期时间的小时差异

考虑使用 SQL Server 函数来计算两个 datetime 值之间的时差，以小时为单位：

SQL Server：

— Difference between Dec 30, 2011 08:55 and Dec 30, 2011 9:05 in weeks
SELECT DATEDIFF(hour, \’2011-12-30 08:55\’, \’2011-12-30 09:05\’);
— Result: 1

请注意，尽管 datetime 值之间只有 10 分钟的差异，但 DATEDIFF 返回了 1 小时。

在 PostgreSQL 中，您可以使用表达式来定义天数（请参见上文），乘以 24 并乘以小时。

PostgreSQL：

— Difference between Dec 30, 2011 08:55 and Dec 30, 2011 9:05 in weeks
SELECT DATE_PART(\’day\’, \’2011-12-30 08:55\’::timestamp – \’2011-12-30 09:05\’::timestamp) * 24 +
DATE_PART(\’hour\’, \’2011-12-30 08:55\’::timestamp – \’2011-12-30 09:05\’::timestamp);
— Result: 0

请注意，此 PostreSQL 表达式返回在 datetime 值之间传递的完整小时数。

PostgreSQL-分钟中的日期时间差异

考虑使用 SQL Server 函数以分钟为单位计算两个日期时间值之间的差：

SQL Server：

— Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in minutes
SELECT DATEDIFF(minute, \’2011-12-30 08:54:55\’, \’2011-12-30 08:56:10\’);
— Result: 2

— Time only
SELECT DATEDIFF(minute, \’08:54:55\’, \’08:56:10\’);
— Result: 2

请注意，尽管 datetime 值之间只有 1 分 15 秒，但 DATEDIFF 返回了 2 分钟。

在 PostgreSQL 中，您可以使用一个表达式来定义小时数（请参阅上文），乘以 60 并乘以分钟。

PostgreSQL：

— Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in minutes
SELECT (DATE_PART(\’day\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp) * 24 +
DATE_PART(\’hour\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp)) * 60 +
DATE_PART(\’minute\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp);
— Result: 1

— Time only
SELECT DATE_PART(\’hour\’, \’08:56:10\’::time – \’08:54:55\’::time) * 60 +
DATE_PART(\’minute\’, \’08:56:10\’::time – \’08:54:55\’::time);
— Result: 1

请注意，这些 PostreSQL 表达式返回在 datetime 值之间传递的完整分钟数。

PostgreSQL-日期时间差（以秒为单位）

考虑使用 SQL Server 函数以秒为单位计算两个日期时间值之间的差：

SQL Server：

— Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in seconds
SELECT DATEDIFF(second, \’2011-12-30 08:54:55\’, \’2011-12-30 08:56:10\’);
— Result: 75

— Time only
SELECT DATEDIFF(second, \’08:54:55\’, \’08:56:10\’);
— Result: 75

在 PostgreSQL 中，您可以使用表达式定义分钟数（请参见上文），乘以 60 并乘以秒。

PostgreSQL：

— Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in seconds
SELECT ((DATE_PART(\’day\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp) * 24 +
DATE_PART(\’hour\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp)) * 60 +
DATE_PART(\’minute\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp)) * 60 +
DATE_PART(\’second\’, \’2011-12-30 08:56:10\’::timestamp – \’2011-12-30 08:54:55\’::timestamp);
— Result: 75

— Time only
SELECT (DATE_PART(\’hour\’, \’08:56:10\’::time – \’08:54:55\’::time) * 60 +
DATE_PART(\’minute\’, \’08:56:10\’::time – \’08:54:55\’::time)) * 60 +
DATE_PART(\’second\’, \’08:56:10\’::time – \’08:54:55\’::time);
— Result: 75

PostgreSQL DATEDIFF-用户定义函数（UDF）

除了使用单独的表达式来计算每个时间单位的日期时间差之外，还可以使用类似于 SQL Server DATEDIFF 函数的函数。

PostgreSQL：

CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP)
RETURNS INT AS $$
DECLARE
diff_interval INTERVAL;
diff INT = 0;
years_diff INT = 0;
BEGIN
IF units IN (\’yy\’, \’yyyy\’, \’year\’, \’mm\’, \’m\’, \’month\’) THEN
years_diff = DATE_PART(\’year\’, end_t) – DATE_PART(\’year\’, start_t);

IF units IN (\’yy\’, \’yyyy\’, \’year\’) THEN
— SQL Server does not count full years passed (only difference between year parts)
RETURN years_diff;
ELSE
— If end month is less than start month it will subtracted
RETURN years_diff * 12 + (DATE_PART(\’month\’, end_t) – DATE_PART(\’month\’, start_t));
END IF;
END IF;

— Minus operator returns interval \’DDD days HH:MI:SS\’
diff_interval = end_t – start_t;

diff = diff + DATE_PART(\’day\’, diff_interval);

IF units IN (\’wk\’, \’ww\’, \’week\’) THEN
diff = diff/7;
RETURN diff;
END IF;

IF units IN (\’dd\’, \’d\’, \’day\’) THEN
RETURN diff;
END IF;

diff = diff * 24 + DATE_PART(\’hour\’, diff_interval);

IF units IN (\’hh\’, \’hour\’) THEN
RETURN diff;
END IF;

diff = diff * 60 + DATE_PART(\’minute\’, diff_interval);

IF units IN (\’mi\’, \’n\’, \’minute\’) THEN
RETURN diff;
END IF;

diff = diff * 60 + DATE_PART(\’second\’, diff_interval);

RETURN diff;
END;
$$ LANGUAGE plpgsql;

如何使用 PostgreSQL DATEDIFF 函数

语法与 SQL Server DATEDIFF 相似，但是您必须在 PostgreSQL 中将时间单位（秒，分钟等及其缩写）指定为字符串文字，例如：

— Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in seconds
SELECT DATEDIFF(\’second\’, \’2011-12-30 08:54:55\’::timestamp, \’2011-12-30 08:56:10\’::timestamp);
— Result: 75

PostgreSQL DATEDIFF 函数仅适用于 TIME

您可以具有另一个仅对时间数据类型起作用的函数。PostgreSQL 支持具有相同名称但参数数据类型不同的重载函数：

CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIME, end_t TIME)
RETURNS INT AS $$
DECLARE
diff_interval INTERVAL;
diff INT = 0;
BEGIN
— Minus operator for TIME returns interval \’HH:MI:SS\’
diff_interval = end_t – start_t;

diff = DATE_PART(\’hour\’, diff_interval);

IF units IN (\’hh\’, \’hour\’) THEN
RETURN diff;
END IF;

diff = diff * 60 + DATE_PART(\’minute\’, diff_interval);

IF units IN (\’mi\’, \’n\’, \’minute\’) THEN
RETURN diff;
END IF;

diff = diff * 60 + DATE_PART(\’second\’, diff_interval);

RETURN diff;
END;
$$ LANGUAGE plpgsql;

登录后复制

例如，可以将此函数调用为：

— Difference between 08:54:55 and 08:56:10 in seconds
SELECT DATEDIFF(\’second\’, \’08:54:55\’::time, \’08:56:10\’::time);
— Result: 75