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

PostgreSQL-DATEDIFF-日期时间差,以秒,天,月,周等为单位

您可以使用各种日期时间表达式或用户定义的 DATEDIFF 函数(UDF)在 PostgreSQL 中计算两个日期时间值之间的差,以秒,分钟,小时,天,周,月和年为单位。

总览

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

SQL Server
and Sybase
PostgreSQL
YearsDATEDIFF(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 值,格式为“ ddd days hh: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

总结

作者:Zhang.Voi原文地址:https://blog.csdn.net/qq_35222843/article/details/127927404

%s 个评论

要回复文章请先登录注册