PostgreSQL日期時間間隔計算：使用DATEDIFF函數(shù)的示例解釋

目錄

PostgreSQL-DATEDIFF-日期時間差，以秒，天，月，周等為單位

您可以使用各種日期時間表達式或用戶定義的 DATEDIFF 函數(shù)（UDF）在 PostgreSQL 中計算兩個日期時間值之間的差，以秒，分鐘，小時，天，周，月和年為單位。

總覽

PostgreSQL 不提供類似于 SQL Server DATEDIFF 的[2] DATEDIFF 函數(shù)，但是您可以使用各種表達式或 UDF 來獲得相同的結(jié)果。

| | 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 函數(shù)來計算以年為單位的兩個日期之間的差：

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 函數(shù)返回 1 年，盡管日期之間只有 3 個月。

SQL Server 不計算日期之間經(jīng)過的整年，它僅計算年份之間的差異。

在 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 函數(shù)來計算兩個日期（以月為單位）之間的差額：

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-日期的天數(shù)差異

考慮使用 SQL Server 函數(shù)來計算兩天之間的日期差：

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 數(shù)據(jù)類型），則將獲得一個 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 數(shù)函數(shù) extact 的天數(shù)，但它返回的數(shù)量充分的日期之間的天數(shù)。

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 函數(shù)來計算兩周中兩個日期之間的差額：

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 返回日期時間值之間的整周數(shù)。

在 PostgreSQL 中，您可以使用表達式定義天數(shù)（請參見上文）并將其除以 7。需要 TRUNC 才能刪除除后的小數(shù)部分。

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 函數(shù)來計算兩個 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 中，您可以使用表達式來定義天數(shù)（請參見上文），乘以 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 值之間傳遞的完整小時數(shù)。

PostgreSQL-分鐘中的日期時間差異

考慮使用 SQL Server 函數(shù)以分鐘為單位計算兩個日期時間值之間的差：

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 中，您可以使用一個表達式來定義小時數(shù)（請參閱上文），乘以 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 值之間傳遞的完整分鐘數(shù)。

PostgreSQL-日期時間差（以秒為單位）

考慮使用 SQL Server 函數(shù)以秒為單位計算兩個日期時間值之間的差：

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 中，您可以使用表達式定義分鐘數(shù)（請參見上文），乘以 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-用戶定義函數(shù)（UDF）

除了使用單獨的表達式來計算每個時間單位的日期時間差之外，還可以使用類似于 SQL Server DATEDIFF 函數(shù)的函數(shù)。

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 函數(shù)

語法與 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 函數(shù)僅適用于 TIME

您可以具有另一個僅對時間數(shù)據(jù)類型起作用的函數(shù)。PostgreSQL 支持具有相同名稱但參數(shù)數(shù)據(jù)類型不同的重載函數(shù)：

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;

例如，可以將此函數(shù)調(diào)用為：

-- 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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容