如何计算 PostgreSQL 中的 DATE 差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24929735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to calculate DATE Difference in PostgreSQL?
提问by Meem
Here I need to calculate the difference of the two dates in the PostgreSQL.
这里我需要计算PostgreSQL.
In SQL Server: Like we do in SQL Serverits much easier.
在 SQL Server 中：就像我们在SQL Server它中所做的那样容易得多。
DATEDIFF(Day, MIN(joindate), MAX(joindate)) AS DateDifference;
My Try: I am trying using the following script:
我的尝试：我正在尝试使用以下脚本：
(Max(joindate) - Min(joindate)) as DateDifference;
Question:
问题：
Is my method correct?
Is there any function in
PostgreSQLto calculate this?
我的方法正确吗？
有什么函数
PostgreSQL可以计算这个吗？
回答by Joachim Isaksson
Your calculation is correct for DATEtypes, but if your values are timestamps, you should probably use EXTRACT(or DATE_PART) to be sure to get only the difference in fulldays;
您的计算对于DATE类型是正确的，但如果您的值是时间戳，您可能应该使用EXTRACT(或 DATE_PART) 以确保仅获得全天的差异；
EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference
An SQLfiddle to test with. Note the timestamp difference being 1 second less than 2 full days.
一个用于测试的 SQLfiddle。请注意时间戳差异是 1 秒比 2 整天少。
回答by Frank Heikens
CAST both fields to datatype DATE and you can use a minus:
将两个字段都转换为 DATE 数据类型，您可以使用减号：
(CAST(MAX(joindate) AS date) - CAST(MIN(joindate) AS date)) as DateDifference
Test case:
测试用例：
SELECT (CAST(MAX(joindate) AS date) - CAST(MIN(joindate) AS date)) as DateDifference
FROM
generate_series('2014-01-01'::timestamp, '2014-02-01'::timestamp, interval '1 hour') g(joindate);
Result: 31
结果：31
Or create a function datediff():
或者创建一个函数 datediff()：
CREATE OR REPLACE FUNCTION datediff(timestamp, timestamp)
RETURNS int
LANGUAGE sql
AS
$$
SELECT CAST( AS date) - CAST( AS date) as DateDifference
$$;
回答by anoraq
This is how I usually do it. A simple number of days perspective of B minus A.
这就是我通常的做法。B减去A的简单天数透视。
DATE_PART('day', MAX(joindate) - MIN(joindate)) as date_diff
回答by zubair-0
a simple way would be to cast the dates into timestamps and take their difference and then extract the DAY part.
一种简单的方法是将日期转换为时间戳并取它们的差异，然后提取 DAY 部分。
if you want real difference
如果你想要真正的区别
select extract(day from 'DATE_A'::timestamp - 'DATE_B':timestamp);
if you want absolute difference
如果你想要绝对的差异
select abs(extract(day from 'DATE_A'::timestamp - 'DATE_B':timestamp));
