PostgreSQL日期时间间隔计算：使用DATEDIFF函数的示例解释

总览

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

| | SQL Server
and Sybase | PostgreSQL |
| --- | --- | --- |
| Years | DATEDIFF(yy, start, end) | DATE_PART('year', end) - DATE_PART('year', start) |
| Months | DATEDIFF(mm, start, end) | years_diff * 12 + (DATE_PART('month', end) - DATE_PART('month', start)) |
| Days | DATEDIFF(dd, start, end) | DATE_PART('day', end - start) |
| Weeks | DATEDIFF(wk, start, end) | TRUNC(DATE_PART('day', end - start)/7) |
| Hours | DATEDIFF(hh, start, end) | days_diff * 24 + DATE_PART('hour', end - start ) |
| Minutes | DATEDIFF(mi, start, end) | hours_diff * 60 + DATE_PART('minute', end - start ) |
| Seconds | DATEDIFF(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

参考：https://www.45fan.com/article.php?aid=1HwRAHhXML6U81U4

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,242评论 5赞 459
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,769评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,484评论 0赞 319
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,133评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,007评论 4赞 355
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,080评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,496评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,190评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,464评论 1赞 290
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,549评论 2赞 309
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,330评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,205评论 3赞 312
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,567评论 3赞 298
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,889评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,160评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,475评论 2赞 341
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,650评论 2赞 335

PostgreSQL日期时间间隔计算：使用DATEDIFF函数的示例解释

PostgreSQL日期时间间隔计算：使用DATEDIFF函数的示例解释

目录

总览

PostgreSQL-年中的日期差异

PostgreSQL-月中的日期差异

PostgreSQL-日期的天数差异

PostgreSQL-周中的日期差异

PostgreSQL-日期时间的小时差异

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

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

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

如何使用 PostgreSQL DATEDIFF 函数

PostgreSQL DATEDIFF 函数仅适用于 TIME

推荐阅读更多精彩内容