PostgreSQL的日期時(shí)間差DATEDIFF實(shí)例詳解
PostgreSQL-DATEDIFF-日期時(shí)間差，以秒，天，月，周等為單位
您可以使用各種日期時(shí)間表達(dá)式或用戶(hù)定義的 DATEDIFF 函數(shù)（UDF）在 PostgreSQL 中計(jì)算兩個(gè)日期時(shí)間值之間的差，以秒，分鐘，小時(shí)，天，周，月和年為單位。
總覽
PostgreSQL 不提供類(lèi)似于 SQL Server DATEDIFF 的[2] DATEDIFF 函數(shù)，但是您可以使用各種表達(dá)式或 UDF 來(lái)獲得相同的結(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ù)來(lái)計(jì)算以年為單位的兩個(gè)日期之間的差：
SQL Server：
-- Difference between Oct 02, 2011 and Jan 01, 2012 in years SELECT DATEDIFF(year, '2011-10-02', '2012-01-01'); -- Result: 1
請(qǐng)注意，SQL Server DATEDIFF 函數(shù)返回 1 年，盡管日期之間只有 3 個(gè)月。
SQL Server 不計(jì)算日期之間經(jīng)過(guò)的整年，它僅計(jì)算年份之間的差異。
在 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: 1PostgreSQL-月中的日期差異
考慮使用 SQL Server 函數(shù)來(lái)計(jì)算兩個(gè)日期（以月為單位）之間的差額：
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，然后將月份部分之間的差值相加（可以為負(fù)）。
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: 3PostgreSQL-日期的天數(shù)差異
考慮使用 SQL Server 函數(shù)來(lái)計(jì)算兩天之間的日期差：
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
請(qǐng)注意，DATEDIFF 返回了 2 天，盡管 datetime 值之間只有 1 天 2 小時(shí)。
在 PostgreSQL 中，如果您從另一個(gè)中減去一個(gè)日期時(shí)間值（TIMESTAMP，DATE 或 TIME 數(shù)據(jù)類(lèi)型），則將獲得一個(gè) 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: 1PostgreSQL-周中的日期差異
考慮使用 SQL Server 函數(shù)來(lái)計(jì)算兩周中兩個(gè)日期之間的差額：
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í)間值之間的整周數(shù)。
在 PostgreSQL 中，您可以使用表達(dá)式定義天數(shù)（請(qǐng)參見(jiàn)上文）并將其除以 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: 1PostgreSQL-日期時(shí)間的小時(shí)差異
考慮使用 SQL Server 函數(shù)來(lái)計(jì)算兩個(gè) datetime 值之間的時(shí)差，以小時(shí)為單位：
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
請(qǐng)注意，盡管 datetime 值之間只有 10 分鐘的差異，但 DATEDIFF 返回了 1 小時(shí)。
在 PostgreSQL 中，您可以使用表達(dá)式來(lái)定義天數(shù)（請(qǐng)參見(jiàn)上文），乘以 24 并乘以小時(shí)。
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請(qǐng)注意，此 PostreSQL 表達(dá)式返回在 datetime 值之間傳遞的完整小時(shí)數(shù)。
PostgreSQL-分鐘中的日期時(shí)間差異
考慮使用 SQL Server 函數(shù)以分鐘為單位計(jì)算兩個(gè)日期時(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
請(qǐng)注意，盡管 datetime 值之間只有 1 分 15 秒，但 DATEDIFF 返回了 2 分鐘。
在 PostgreSQL 中，您可以使用一個(gè)表達(dá)式來(lái)定義小時(shí)數(shù)（請(qǐng)參閱上文），乘以 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請(qǐng)注意，這些 PostreSQL 表達(dá)式返回在 datetime 值之間傳遞的完整分鐘數(shù)。
PostgreSQL-日期時(shí)間差（以秒為單位）
考慮使用 SQL Server 函數(shù)以秒為單位計(jì)算兩個(gè)日期時(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 中，您可以使用表達(dá)式定義分鐘數(shù)（請(qǐng)參見(jiàn)上文），乘以 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: 75PostgreSQL DATEDIFF-用戶(hù)定義函數(shù)（UDF）
除了使用單獨(dú)的表達(dá)式來(lái)計(jì)算每個(gè)時(shí)間單位的日期時(shí)間差之外，還可以使用類(lèi)似于 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ù)
語(yǔ)法與 SQL Server DATEDIFF 相似，但是您必須在 PostgreSQL 中將時(shí)間單位（秒，分鐘等及其縮寫(xiě)）指定為字符串文字，例如：
-- 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: 75PostgreSQL DATEDIFF 函數(shù)僅適用于 TIME
您可以具有另一個(gè)僅對(duì)時(shí)間數(shù)據(jù)類(lèi)型起作用的函數(shù)。PostgreSQL 支持具有相同名稱(chēng)但參數(shù)數(shù)據(jù)類(lèi)型不同的重載函數(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;
登錄后復(fù)制
例如，可以將此函數(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總結(jié)
到此這篇關(guān)于PostgreSQL日期時(shí)間差DATEDIFF的文章就介紹到這了,更多相關(guān)PostgreSQL日期時(shí)間差DATEDIFF內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
postgresql數(shù)據(jù)庫(kù)連接數(shù)和狀態(tài)查詢(xún)操作
這篇文章主要介紹了postgresql數(shù)據(jù)庫(kù)連接數(shù)和狀態(tài)查詢(xún)操作，具有很好的參考價(jià)值，對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
對(duì)Postgresql中的json和array使用介紹
這篇文章主要介紹了對(duì)Postgresql中的json和array使用介紹，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
phpPgAdmin 配置文件參數(shù)說(shuō)明中文版
這篇文章主要介紹了phpPgAdmin配置文件config.inc.php-dist中注釋的詳細(xì)說(shuō)明，并翻譯成中文,需要的朋友可以參考下2014-03-03
PostgreSQL被稱(chēng)為是“最高級(jí)的開(kāi)源數(shù)據(jù)庫(kù)”，它的數(shù)據(jù)類(lèi)型非常豐富，用它來(lái)解決一些比較偏門(mén)的需求非常適合。由于PgSQL國(guó)內(nèi)的資料較少，所有本文將詳細(xì)介紹此類(lèi)問(wèn)題，以便后來(lái)的同學(xué)能順利使用PgSQL。2021-05-05
CVE-2019-9193之PostgreSQL?任意命令執(zhí)行漏洞的問(wèn)題
這篇文章主要介紹了CVE-2019-9193：PostgreSQL?任意命令執(zhí)行漏洞,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-08-08
postgresql數(shù)據(jù)庫(kù)如何查看數(shù)據(jù)中表的信息
這篇文章主要給大家介紹了關(guān)于postgresql數(shù)據(jù)庫(kù)如何查看數(shù)據(jù)中表信息的相關(guān)資料,要查詢(xún)數(shù)據(jù)表信息,需要用到 系統(tǒng)表或系統(tǒng)視圖等,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-04-04
這篇文章主要介紹了postgresql查詢(xún)鎖表以及解除鎖表操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
解決PostgreSQL 執(zhí)行超時(shí)的情況
這篇文章主要介紹了解決PostgreSQL 執(zhí)行超時(shí)的情況，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL數(shù)據(jù)庫(kù)事務(wù)插入刪除及更新操作示例
這篇文章主要為大家介紹了PostgreSQL事務(wù)的插入刪除及更新操作示例，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪2022-04-04
玩轉(zhuǎn)PostgreSQL之30個(gè)實(shí)用SQL語(yǔ)句
本文主要整理總結(jié)了30個(gè)實(shí)用SQL，方便大家可以高效利用PostgreSQL,需要的朋友可以參考下2022-11-11